Countif to use like sumif
 

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

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

Countif to use like sumif
 

Ture,

Thank you very much for that, works fine now.

Pantelis
"Ture Magnusson" <tureATturedataDOTse.nospam.please wrote in message
...
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

Countif to use like sumif
 

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

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"pantelis" wrote in message
...
Ture,

Thank you very much for that, works fine now.

Pantelis
"Ture Magnusson" <tureATturedataDOTse.nospam.please wrote in message
...
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