Hi,

I have the following formula:

=COUNTIF(August!I:I,"NSW")

I need help editing this. I only want it to return the values IF:

August!E:E,"DEN"

Can anyone assist?

=Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW"))

If you don't have XL 2007, Sumproduct will not accept an entire column, such
as E:E, but E1:E65535 would work.


"Tammy" wrote:

Hi,

I have the following formula:

=COUNTIF(August!I:I,"NSW")

I need help editing this. I only want it to return the values IF:

August!E:E,"DEN"

Can anyone assist?

Hi JMB,

This worked perfectly, I did not realise that I also have:

=COUNTA(August!AT:AV)-3

The sumproduct does not work for this, I must be doing something wrong, can
you assist?

Thanks
Tammy

"JMB" wrote:

=Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW"))

If you don't have XL 2007, Sumproduct will not accept an entire column, such
as E:E, but E1:E65535 would work.


"Tammy" wrote:

Hi,

I have the following formula:

=COUNTIF(August!I:I,"NSW")

I need help editing this. I only want it to return the values IF:

August!E:E,"DEN"

Can anyone assist?

maybe - can you describe how you want to modify the counta function?

=Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3
should yield same results as the counta function

if you want to add a condition similar to the previous question:
=Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3

should count all cells in AT1:AV100 that are not *empty* and have "DEN" in
column E. Note that CountA counts the empty string (ie "") - so you'll need
to consider that if you have formulas in column E that return "". Since the
two areas differ with respect to the number of columns, you have to multiply
the two arrays together yourself to create one array to feed to the
sumproduct function.

My browser is having problems, so I can't give the exact link. But if you
can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a
link (bottom left) for Sumproduct (multiple condition tests). It's worth
reading.


"Tammy" wrote:

Hi JMB,

This worked perfectly, I did not realise that I also have:

=COUNTA(August!AT:AV)-3

The sumproduct does not work for this, I must be doing something wrong, can
you assist?

Thanks
Tammy

"JMB" wrote:

=Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW"))

If you don't have XL 2007, Sumproduct will not accept an entire column, such
as E:E, but E1:E65535 would work.


"Tammy" wrote:

Hi,

I have the following formula:

=COUNTIF(August!I:I,"NSW")

I need help editing this. I only want it to return the values IF:

August!E:E,"DEN"

Can anyone assist?

Ok I'll try to explain better, I am not very good with words:

I have a dashboard with charts and tables feeding off some raw data. I have
2 different types formulas in the dashboard:
=COUNTA(August!AT:AV)-3
=COUNTIF(August!I:I,"NSW")

I also have some buttons/macros that the user can click to load each month's
data. That was simple enough. Now they want to be able to load the data from
each month with a limit on only seeing the DEN data which is in column E of
the raw data.

So I only want each of these to give results of the previous 2 types of
formulas if the following is TRUE:
EXAMPLE: (August!E:E,"DEN")

Does this make sense? I'll try that site too, thanks for that.

"JMB" wrote:

maybe - can you describe how you want to modify the counta function?

=Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3
should yield same results as the counta function

if you want to add a condition similar to the previous question:
=Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3

should count all cells in AT1:AV100 that are not *empty* and have "DEN" in
column E. Note that CountA counts the empty string (ie "") - so you'll need
to consider that if you have formulas in column E that return "". Since the
two areas differ with respect to the number of columns, you have to multiply
the two arrays together yourself to create one array to feed to the
sumproduct function.

My browser is having problems, so I can't give the exact link. But if you
can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a
link (bottom left) for Sumproduct (multiple condition tests). It's worth
reading.


"Tammy" wrote:

Hi JMB,

This worked perfectly, I did not realise that I also have:

=COUNTA(August!AT:AV)-3

The sumproduct does not work for this, I must be doing something wrong, can
you assist?

Thanks
Tammy

"JMB" wrote:

=Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW"))

If you don't have XL 2007, Sumproduct will not accept an entire column, such
as E:E, but E1:E65535 would work.


"Tammy" wrote:

Hi,

I have the following formula:

=COUNTIF(August!I:I,"NSW")

I need help editing this. I only want it to return the values IF:

August!E:E,"DEN"

Can anyone assist?

Sounds like you want to modify the counta function to include a condition
that "DEN" appears in column E. I think something similar to this should
work:

=Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3


"Tammy" wrote:

Ok I'll try to explain better, I am not very good with words:

I have a dashboard with charts and tables feeding off some raw data. I have
2 different types formulas in the dashboard:
=COUNTA(August!AT:AV)-3
=COUNTIF(August!I:I,"NSW")

I also have some buttons/macros that the user can click to load each month's
data. That was simple enough. Now they want to be able to load the data from
each month with a limit on only seeing the DEN data which is in column E of
the raw data.

So I only want each of these to give results of the previous 2 types of
formulas if the following is TRUE:
EXAMPLE: (August!E:E,"DEN")

Does this make sense? I'll try that site too, thanks for that.

"JMB" wrote:

maybe - can you describe how you want to modify the counta function?

=Sumproduct(--(Not(IsBlank(August!AT1:AV100))))-3
should yield same results as the counta function

if you want to add a condition similar to the previous question:
=Sumproduct((Not(IsBlank(August!AT1:AV100)))*(Augu st!E1:E100="DEN"))-3

should count all cells in AT1:AV100 that are not *empty* and have "DEN" in
column E. Note that CountA counts the empty string (ie "") - so you'll need
to consider that if you have formulas in column E that return "". Since the
two areas differ with respect to the number of columns, you have to multiply
the two arrays together yourself to create one array to feed to the
sumproduct function.

My browser is having problems, so I can't give the exact link. But if you
can, I'd suggest going to Bob Phillips site, xldynamic.com. You'll see a
link (bottom left) for Sumproduct (multiple condition tests). It's worth
reading.


"Tammy" wrote:

Hi JMB,

This worked perfectly, I did not realise that I also have:

=COUNTA(August!AT:AV)-3

The sumproduct does not work for this, I must be doing something wrong, can
you assist?

Thanks
Tammy

"JMB" wrote:

=Sumproduct(--(August!E1:E100="DEN"), --(August!I1:I100="NSW"))

If you don't have XL 2007, Sumproduct will not accept an entire column, such
as E:E, but E1:E65535 would work.


"Tammy" wrote:

Hi,

I have the following formula:

=COUNTIF(August!I:I,"NSW")

I need help editing this. I only want it to return the values IF:

August!E:E,"DEN"

Can anyone assist?