MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Countif Function - Nightmare


Posted by Lorraine Silvester on September 28, 2001 11:55 AM

I have to use the countif function to work out the value in a cell using two ranges. The first range contains an alphabetical list of codes from A to H, the second range contains a list of dates i.e. 10/3/01 or 11/4/01. I have to count all the different code totals for the cells containing November dates. Confused - so am I.

Please help.


Posted by Juan Pablo on September 28, 2001 12:03 PM

If you have to count ALL the codes that appear in the first column that fall in a particular month (November) use this formula.

Range of codes in A2:A100, Dates B2:B100 if not change acordingly

In C1 put

=SUMPRODUCT((MONTH(B2:B100)=11)*1)

If you have to count, for example, every appearance of "B" in column A, that also falls in november

=SUMPRODUCT((MONTH(B2:B100)=11)*(A2:A100="B"))

HTH

Juan Pablo

---------------

Posted by Aladin Akyurek on September 28, 2001 12:07 PM

Lorraine --

Please specify the ranges as they are: So you have codes in A?:H?. And dates in some range: What is the address of this range? What do you mean by code totals: Counting them according to some criterion?

Aladin