SUMIF or SUMPRODUCT?

E

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
 
G

Glenn

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

Regards

EricB


D2:D1000 can't equal both "Admin" and "Tech".
 
M

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

T. Valko

The problem is here:

--('TB SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="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: D2:D1000 = 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"),(D2:D1000="Admin")+(D2:D1000="Tech"),E2:E1000)
 
E

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
 
B

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((D2:D1000="Admin")+(D2:D1000="Tech")),E2:E1000)

Regards,
Bernd
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top