Help with a formula

[Tammy]

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?

 

[JMB]

=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]

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]

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)))*(August!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]

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]

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)))*(August!E1:E100="DEN"))-3

 

[Tammy]

Sorry I must be soooo blonde, you already said that in the previous post...
this works perfect... thanks a million!!!

 

[JMB]

That's okay - I asked for details before taking my shot in the dark. Glad to
hear you got it working.

 

[Tammy]

Hi again,

Can I bug you one last time... I have been trying to figure it out with no
luck.

On one tab your formula works perfect:

=Sumproduct(--(August!E1:E65535="DEN"), --(August!I1:I65535="NSW"))

On another tab, I change the following formula:

=COUNTIF(August!$AU:$AW,"Access Process")

and it returns VALUE#? See below what I use.

=Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access
Process"))


???

 

[JMB]

Try:
=Sumproduct((August!E1:E65535="DEN")*(August!$AU1:$AW65535="Access Process"))

It's a subtle difference.

This version that you tried
=Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access
Process"))

is a good attempt, but it has two arguments which the sumproduct function
will try to multiply together and add up the results. the problem is
sumproduct likes its arguments to be the same dimensions, which these are not
(first argument is one column and the second argument is three columns). but
you should be able to get around it by explicitly multiplying the arrays
(ranges) together yourself before sumproduct gets its hands on it. the
result of your multiplication gets passed to sumproduct and it should happily
add it up (no multiplication necessary on the part of the sumproduct function
because only one array gets passed to it).

basically, it's a two step process. multiplying the two arrays together and
adding up the results. sumproduct is capable of doing both steps if the
arrays are the same dimensions. if not, you have to do the multiplication
and let sumproduct do the addition only.

 

[Tammy]

What can I say, you are an absolute legend...

Try:
=Sumproduct((August!E1:E65535="DEN")*(August!$AU1:$AW65535="Access Process"))

It's a subtle difference.

This version that you tried
=Sumproduct(--(August!E1:E65535="DEN"), --(August!$AU1:$AW65535="Access
Process"))

is a good attempt, but it has two arguments which the sumproduct function
will try to multiply together and add up the results. the problem is
sumproduct likes its arguments to be the same dimensions, which these are not
(first argument is one column and the second argument is three columns). but
you should be able to get around it by explicitly multiplying the arrays
(ranges) together yourself before sumproduct gets its hands on it. the
result of your multiplication gets passed to sumproduct and it should happily
add it up (no multiplication necessary on the part of the sumproduct function
because only one array gets passed to it).

basically, it's a two step process. multiplying the two arrays together and
adding up the results. sumproduct is capable of doing both steps if the
arrays are the same dimensions. if not, you have to do the multiplication
and let sumproduct do the addition only.

 

[JMB]

just passing along knowledge i've gleaned/stolen from some very smart people
who hang out here.