Countif to use like sumif

[pantelis]

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

 

[Ture Magnusson]

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:B6>0))

Ture Magnusson
Karlstad, Sweden

 

[pantelis]

Ture,

Thank you very much for that, works fine now.

Pantelis

 

[Ture Magnusson]

You are welcome, Pantelis! I'm happy that I could help you.