Count using a criteria

D

Dan Colgan

I have a spreadsheet in which I need to tabulate a total
count of rows which meet a criteria.

Example:
Type Phase SubPhase
"Occupied" 1 A

I need a formula that counts the rows that meet a criteria
such as Where phase is 1 and Subphase is A, count the
number of rows that are "Occupied"

Can anyone help - this seems like it should be easier. I
may be missing something

Thanks
 
M

Mark Graesser

Hi Dan
You can't use the COUTIF function since you have more than one criteria. The SUMPRODUCT function will do what you need

=SUMPRODUCT((A1:A100="Occupied")*(B1:B100=1)*(C1:C100=A")

Keep in mind that all of the ranges must be the same size

Good Luck
Mark Graesse
[email protected]
Boston M


----- Dan Colgan wrote: ----

I have a spreadsheet in which I need to tabulate a total
count of rows which meet a criteria.

Example:
Type Phase SubPhas
"Occupied" 1

I need a formula that counts the rows that meet a criteria
such as Where phase is 1 and Subphase is A, count the
number of rows that are "Occupied

Can anyone help - this seems like it should be easier. I
may be missing somethin

Thank
 
D

Dan Colgan

Thanks for the help. I am having trouble getting the
formula into the field. It shows up as the contents of
the cell. Is there a special entry
 
D

Dan Colgan

For some reason I cannot get the formula to go in the
cell. It shows the text of the formula but won't execute.

Is there something I'm missing

Dan
 
P

Peo Sjoblom

The cell(s) you are putting the formula in is formatted (or has been) as
text, format it as general,
then do edit>replace and replace the equal sign with the equal sign, that
usually forces a calculation
 
D

Dan Colgan

OK sorry to be a pest. That was it.
Unfortunately now that the formula is in, it returns only
a zero. I have verified by filter that there are 3 records
that meet the criteria, any suggestions..

Dan
-----Original Message-----
Hi Dan,
Sounds like your cell is formatted as text. Check the
Click to expand...
number format, change it to general, and re-enter the
formula.
 
M

Mark Graesser

Hi Dan
Since your formula cell was formatted as text I would imagine that column B is also formatted as text. The quickest fix is to put the 1 in the formula inside quotes, so change the formula to

=SUMPRODUCT(--(A2:A100="Occupied"),--(B2:B100="1"),--(C2:C100="A")

A better fix would be to convert column B into numbers. Select a blank cell and Copy it. Then select column B and Paste_Special>Add. This will add zero to all of the numbers and convert them from text into numbers

Good Luck
Mark Graesse
[email protected]
Boston M

----- Dan Colgan wrote: ----

OK sorry to be a pest. That was it
Unfortunately now that the formula is in, it returns only
a zero. I have verified by filter that there are 3 records
that meet the criteria, any suggestions.

Da
-----Original Message----
Hi Dan
Sounds like your cell is formatted as text. Check the
Click to expand...
number format, change it to general, and re-enter the
formula
 
Top