Countif to use like sumif
Panetlis,
Use SUMPRODUCT to combine and find the count
using more than one criteria, in a way similar to this:
=SUMPRODUCT(N(A1:A6=1999),N(B1:B60))
Ture Magnusson
Karlstad, Sweden
"pantelis" wrote in message
...
Hi all,
Need some help with this problem that has been bugging me.
I have the following data
ColA ColB ColC
1999 100 150
1999 0 75
2000 150 200
2001 200 250
2001 0 100
2001 0 100
I need to look down column A and return the number of data points 0
(Count) in column B & C i.e.
1999 1 2
2000 1 1
2001 1 3
If countif worked like sumif I would do something like
countif(ColA,"2001",ColB)=1, countif(ColA,"2001",ColC)=3.
Unfortunately countif does not work like this so I need another way to get
a
formula to count the number of data points in the columns B&C that meet
the
conditions in column A.
Could I program a similar function in VBA.
Any help will be seriously appreciated.
Thanks
Pantelis
|