What is the best way to Query a flat file

  • Thread starter Thread starter frogman7
  • Start date Start date
F

frogman7

I have a flat data file.
Name status

Jones started
Johnson started
Smith waiting
Jones closed
Johnson waiting
Smith closed
Jones started
Johnson waiting
Smith waiting

I want to count the number of time Jones has the status of closed.

is this possible without using VBA
if(A:A="Jones", countif(B:B, "closed"), "")

I am trying to find the best thing to use.
VBA
Lookup Tables
nested IFs
Thank you
 
If you're using xl2007, look at =countifs() in excel's help.

This will work in all versions:

=sumproduct(--(a1:a99="Jones"),--(b1:b99="closed"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

======
An alternative for you may be to use data|pivottable. You could get a nice
table using the names in the row field, the status in the column field and count
of names in the data field.
 
If you're using xl2007, look at =countifs() in excel's help.

This will work in all versions:

=sumproduct(--(a1:a99="Jones"),--(b1:b99="closed"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html

======
An alternative for you may be to use data|pivottable. You could get a nice
table using the names in the row field, the status in the column field and count
of names in the data field.










--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

That it beautiful simple and it works
Thanks
 
Back
Top