COUNTIF multiple conditions are met

Posted by Lewis (1) on February 11, 2002 3:51 PM

I have data on sheet 1
Col A contains dates
Col B TypeA, TypeB, TypeC

On sheet2 I want to set up formulae so that it counts all cells in Col A that are greater than a date entered in A1 on Sheet2 AND that are TypeA, then TypeB etc.

Posted by Aladin Akyurek on February 11, 2002 10:32 PM

I'll assume that, in Sheet1, the data start in row 2 while row 1 holds appropriate labels descriptive of data underneath like Dates, etc.

On Sheet2:

In A1 you have a criterion date.
In B1 enter: =MATCH(9.99999999999999E+307,Sheet1!A:A)-1

Note. The -1 reflects the fact that the actual data start in row 2 of Sheet1.

In A2 enter: TypeA
In A3 enter: TypeB
etc.

In B2 enter: =SUMPRODUCT((OFFSET(Sheet1!A$2,0,0,$B$1,1)>$A$1)*(OFFSET(Sheet1!B$2,0,0,$B$1,1)=$A2))[ copy down this as far as needed ]

Note that this formula can cope automatically with changes to the data in A:B in Sheet1. If the data ranges are fixed (do not change or change seldom), you can delete the formula in B1 and use in B2 a formula with fixed ranges like:

=SUMPRODUCT((Sheet1!A2:A18>$A$1)*(Sheet1!B2:B18=$A2))

Posted by Lewis (1) on February 12, 2002 3:23 AM

The slight problem was my definition of what I wanted to do. I said count if the date was greater than my criterion date, whereas what I really need is greater than _or equal to_ that date

Posted by Aladin Akyurek on February 12, 2002 3:43 AM

Lewis: That's not a big deal. Just change the

>$A$1

bit to:

>=$A$1

Thus:

=SUMPRODUCT((OFFSET(Sheet1!A$2,0,0,$B$1,1)>=$A$1)*(OFFSET(Sheet1!B$2,0,0,$B$1,1)=$A2))

=SUMPRODUCT((Sheet1!A2:A18>=$A$1)*(Sheet1!B2:B18=$A2))

Aladin

==========

Posted by Lewis (1) on February 12, 2002 6:18 AM

Thanks but can you explain something

Posted by Lewis (1) on February 12, 2002 6:20 AM

Thanks but can you explain something (2nd time lucky)

What does =MATCH(9.99999999999999E+307,Sheet1!A:A)-1
do as with my data I get a value of 34 whereas there are in fact only 32 entries in the column?

Posted by Aladin Akyurek on February 12, 2002 8:27 AM

Re: Thanks but can you explain something (2nd time lucky)

Match isn't counting how many entries you have in A. Rather it returns the number of the row holding the last date entry (it's the 35th row in your cse). The 32 entries that you mention suggest that you have blank cells in the range of interest. But that's OK. The -1 part is related to my assumption that the real data (exluding the labels)start at row 2. This assumption is also the reason why the OFFSET bits in the formulas have as their first arg Sheet1!$A$2, etc.

========