COUNTIF for data in 2 columns
Posted by Brad on February 12, 2002 10:15 PM
I'm looking for a way to do a COUNTIF for data in two separate columns. I want to be able to say look for a certain number in column A and then look for a certain date in column B- for a specific date and number (ex: "366" and "2/12/01") I want a count. How can this be done? Thanks.
Posted by Aladin Akyurek on February 12, 2002 11:40 PM
=SUMPRODUCT((A2:A100=E1)*(B2:B100=E2))
where A2:A100 houses numbers, B2:B100 dates, E1 a number like 366, and E2 a date like 2/12/01. E1:E2 holds thus the criteria/conditions that determines what to count.
=================
Posted by Brad on February 13, 2002 5:33 AM
Aladin,
I tried this but I think this is a sum. I was trying to get a count if, for example, there is a match in a row of data (2 separate columns) for "366" and "2/12/02". I can count all the "366"'s, but I can't then ask to go to the next column and filter for date. There may be 50 366's but I want to go to the next column to see how many 366's happened on 2/12/02. If you have any suggestions I'd apprecaite it.
Thanks. =SUMPRODUCT((A2:A100=E1)*(B2:B100=E2))
Posted by Aladin Akyurek on February 13, 2002 7:10 AM
Brad: Please try it again. It produces a count, not a sum (that is, it's not summing either dates or numbers. It just "counts" the co-occurrences of a given number in E1 and a given date in E2.
The following sample is in A1:B7.
{"Nums","Dates";
366,37258;
380,37259;
366,37258;
368,37259;
367,37260;
368,37261}
where big numbers are internal serial numbers for dates, that is:
2-Jan-02
3-Jan-02
2-Jan-02
3-Jan-02
4-Jan-02
5-Jan-02
E1 = 366
E2 = 2-jan-02
=SUMPRODUCT((A2:A7=E1)*(B2:B7=E2))
gives me a count of 2. I thought you were looking for precisely what this formula does.
========== : : =SUMPRODUCT((A2:A100=E1)*(B2:B100=E2))
