Count multiple criteria

[Mike30]

Hello All,

I am hoping someone can help, I have a table on worksheet 1 that contains my data and I am trying to do a front page (totals) on a different worksheet. I am trying to look up and count certain criteria and I can't get it to work.. eg.. worksheet 1 has the below table in and in the worksheet 2 I am trying to count that if Product (column A) = bar then i am trying to count how many are 1-2 weeks, 3-4 weeks etc..
Worksheet 1

product-	14/04
bar	<2weeks
bar	<2weeks
bar	<1week
bar	<3weeks
bar	<2weeks
drink

<tbody>
</tbody>

Worksheet 2

product	min(<1Week & <2weeks)	mid (<3weeks & 4weeks)
bar
crisp
can

<tbody>
</tbody>

Hope this makes sense
Mike

 

[Special-K99]

No cell references so I will assume that Product is in A1 in both sheets

in Sheet2!B2
=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=A2)*((Sheet1!$B$2:$B$1000="<1Week")+(Sheet1!$B$2:$B$1000="<2Weeks")))
and copy down

in Sheet2!C2
=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=A2)*((Sheet1!$B$2:$B$1000="<3Weeks")+(Sheet1!$B$2:$B$1000="<4Weeks")))
and copy down

 

[Mike30]

This works, Excellent

 

[Mike30]

Another quick question, if column b is black but has a formula in the cell how to i count the entire column but ignore the formula?
Thanks
Mike

 

[Special-K99]

Black or blank ? Blank I presume

I dont know a way of counting cells that do not have a formula.

 

[Special-K99]

COUNTA counts non blank cells.

If column D is empty apart from D2 and D4 and D2 is 3 and D4 is blank (but is the result of a formula) then COUNTA(D1:D10) will be 2.
So it's counting D2 and D4 (even though D4 is blank).

So if you wanted to count cells that don't contain a formula or a result just subtract the result from the range of cells, ie 10-COUNTA(D1:D10) = 8

So COUNTA will count non blank cells even if the result is blank but it wont distinguish between a cell that contains a value or a cell that contains a formula. Both will be counted with COUNTA.