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

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

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

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