SUMIF or SUMPRODUCT?

[EricB]

The following formula is returning a ‘0’ outcome:

when using SUMIF I get the desired result but is missing one criteria range ('TB SAICMB'!B2:B1000="'Computer Sales"):

Can I adapt the SUMIF formula to include this range or am I going wring in
my SUMPRODUCT layout?

Regards

EricB

 

[Glenn]

Can I adapt the SUMIF formula to include this range or am I going wring in
my SUMPRODUCT layout?

Regards

EricB

D21000 can't equal both "Admin" and "Tech".

 

[Glenn]

Can I adapt the SUMIF formula to include this range or am I going wring in
my SUMPRODUCT layout?

Regards

EricB

D21000 can't equal both "Admin" and "Tech".

 

[Mike H]

Hi,

There are 2 things. You have an apostrophe before the C in Computer sales,
is that a typo. More importantly you are looking for "Admin" and "Tech" in
column D and it won't be both of those so every row will evaluate as zero.
What are you actually looking for in column D?

Mike

 

[Mike H]

Hi,

There are 2 things. You have an apostrophe before the C in Computer sales,
is that a typo. More importantly you are looking for "Admin" and "Tech" in
column D and it won't be both of those so every row will evaluate as zero.
What are you actually looking for in column D?

Mike

 

[T. Valko]

The problem is here:

--('TB SAICMB'!D21000="Admin"),--('TB SAICMB'!D21000="Tech")

You're testing the same range for 2 different conditions. Since the same
range can't be both conditions at the same time you need to craft the
expression to say: D21000 = Admin or Tech.

Try it like this (I'm leaving out the sheet name so be sure to add it):

=SUMPRODUCT(--(B2:B1000="'Computer
Sales"),(D21000="Admin")+(D21000="Tech"),E2:E1000)

 

[T. Valko]

The problem is here:

--('TB SAICMB'!D21000="Admin"),--('TB SAICMB'!D21000="Tech")

You're testing the same range for 2 different conditions. Since the same
range can't be both conditions at the same time you need to craft the
expression to say: D21000 = Admin or Tech.

Try it like this (I'm leaving out the sheet name so be sure to add it):

=SUMPRODUCT(--(B2:B1000="'Computer
Sales"),(D21000="Admin")+(D21000="Tech"),E2:E1000)

 

[EricB]

Hi Mike H

1) The apostrophe can be considered a typo. I'm re-writing a spreadsheet and
names will be corrected accordingly.
2) It is an either/or situation. In column ‘D’ I need to count values under
“Admin and/or Techâ€. Thus a This + That situation.

I was trying to avoid a situation where I need to us +PLUS: =SUMIF('TB
SAICMB'!B2:B1000,â€Adminâ€,'TB SAICMB'!E2:E1000)+SUMIF('TB
SAICMB'!B2:B1000,â€Techâ€,'TB SAICMB'!E2:E1000)

At present I’m counting under ‘B’, “Computer Sales†– Column ‘D’ reflects
division, ‘Admin’, ‘Tech’, etc. I need to restrict counting to the specifics
in ‘D’ but multiple departments are possible as in this case.

Hope this clarifies.

EricB

Hi Mike H

1) The apostrophe can be considered a typo. I'm re-writing a spreadsheet and
names will be corrected accordingly.
2) It is a

 

[EricB]

Hi Mike H

1) The apostrophe can be considered a typo. I'm re-writing a spreadsheet and
names will be corrected accordingly.
2) It is an either/or situation. In column ‘D’ I need to count values under
“Admin and/or Techâ€. Thus a This + That situation.

I was trying to avoid a situation where I need to us +PLUS: =SUMIF('TB
SAICMB'!B2:B1000,â€Adminâ€,'TB SAICMB'!E2:E1000)+SUMIF('TB
SAICMB'!B2:B1000,â€Techâ€,'TB SAICMB'!E2:E1000)

At present I’m counting under ‘B’, “Computer Sales†– Column ‘D’ reflects
division, ‘Admin’, ‘Tech’, etc. I need to restrict counting to the specifics
in ‘D’ but multiple departments are possible as in this case.

Hope this clarifies.

EricB

Hi Mike H

1) The apostrophe can be considered a typo. I'm re-writing a spreadsheet and
names will be corrected accordingly.
2) It is a

 

[Mike H]

Biff (T.Valko) has shown you how to do it with Sumproduct in his response.

Mike

 

[Mike H]

Biff (T.Valko) has shown you how to do it with Sumproduct in his response.

Mike

 

[Bernd P]

Hello,

In this case a simple addition is ok but generally, if the OR criteria
can both be TRUE, wrap them with SIGN:

=SUMPRODUCT(--(B2:B1000="'Computer
Sales"),SIGN((D21000="Admin")+(D21000="Tech")),E2:E1000)

Regards,
Bernd

 

[Bernd P]

Hello,

In this case a simple addition is ok but generally, if the OR criteria
can both be TRUE, wrap them with SIGN:

=SUMPRODUCT(--(B2:B1000="'Computer
Sales"),SIGN((D21000="Admin")+(D21000="Tech")),E2:E1000)

Regards,
Bernd

 

[T. Valko]

How can one cell contain *both* separate entries at the same time?

 

[T. Valko]

How can one cell contain *both* separate entries at the same time?

 

[Bernd P]

Hello Biff,

I referred to OR criteria, not the same cells. If, for example, you
would need a logical OR on M1:M100 (Y indicating male persons) and on
B1:B100 (Y indicating blonde hair) and if you need to count persons
who are male OR blonde then you don't want to double count blonde AND
male persons.

So =SUMPRODUCT(("Y"=B1:B100)+("Y"=M1:M100)) would be wrong but
=SUMPRODUCT(SIGN(("Y"=B1:B100)+("Y"=M1:M100))) would be ok.

Regards,
Bernd

 

[Bernd P]

Hello Biff,

I referred to OR criteria, not the same cells. If, for example, you
would need a logical OR on M1:M100 (Y indicating male persons) and on
B1:B100 (Y indicating blonde hair) and if you need to count persons
who are male OR blonde then you don't want to double count blonde AND
male persons.

So =SUMPRODUCT(("Y"=B1:B100)+("Y"=M1:M100)) would be wrong but
=SUMPRODUCT(SIGN(("Y"=B1:B100)+("Y"=M1:M100))) would be ok.

Regards,
Bernd

 

[T. Valko]

Ok, I see what you meant.

 

[T. Valko]

Ok, I see what you meant.