IF value

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

If column A, rows 2 through 2,000, contains the date 2/22/08 and column B
(same rows) contains the value 2, I want to count the number of occurrences
of the letters "a" and "m" in column D (same rows).

Please let me know if you can help. Thanks.

Bob
 
I want to count the number of occurrences of
the letters "a" and "m" in column D
Click to expand...

Do you mean "a" *OR* "m" ?

This will count "a" *OR* "m" :

=SUMPRODUCT((A2:A2000=DATE(2008,2,22))*(B2:B2000=2)*(D2:D2000={"A","M"}))

Or, use cells to hold the criteria:

F2 = 2/22/2008
G2 = 2
H2:H3 = A; M

=SUMPRODUCT(--(A2:A2000=F2),--(B2:B2000=G2),--(ISNUMBER(MATCH(D2:D2000,H2:H3,0))))
 
Assuming your 2/22/2008 date is in a cell (E1 for this example), then this
seems to work...

=SUMPRODUCT((A1:A20=E1)*(B1:B20=2)*((D1:D20="a")+(D1:D20="m")))

If you really want to hard-code the date in the formula, then this seems to
work...

=SUMPRODUCT((A1:A20=--"2/22/2008")*(B1:B20=2)*((D1:D20="a")+(D1:D20="m")))

Note that
 
From the way you have described your setup, this might work for you:

=COUNTIF(D:D,"a")+COUNTIF(D:D,"m")

Hope this helps.

Pete
 
Back
Top