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
[TABLE="width: 500"]
<tbody>[TR]
[TD]product-[/TD]
[TD]14/04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]<2weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]<2weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]<1week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]<3weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]<2weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]drink[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]product[/TD]
[TD]min(<1Week & <2weeks)[/TD]
[TD]mid (<3weeks & 4weeks)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]crisp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]can[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.