The following formula is returning a €˜0 outcome:
=SUMPRODUCT(--('TB SAICMB'!B2:B1000="'Computer Sales"),--('TB SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="Tech"),'TB SAICMB'!E2:E1000)

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

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

Regards

EricB

EricB wrote:
The following formula is returning a €˜0 outcome:
=SUMPRODUCT(--('TB SAICMB'!B2:B1000="'Computer Sales"),--('TB SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="Tech"),'TB SAICMB'!E2:E1000)

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

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".

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

"EricB" wrote:

The following formula is returning a €˜0 outcome:
=SUMPRODUCT(--('TB SAICMB'!B2:B1000="'Computer Sales"),--('TB SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="Tech"),'TB SAICMB'!E2:E1000)

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

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

Regards

EricB

The problem is he

--('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:E1 000)

--
Biff
Microsoft Excel MVP


"EricB" wrote in message
...
The following formula is returning a '0' outcome:
=SUMPRODUCT(--('TB SAICMB'!B2:B1000="'Computer Sales"),--('TB
SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="Tech"),'TB
SAICMB'!E2:E1000)

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

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

Regards

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 Im 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" wrote:

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

"EricB" wrote:

The following formula is returning a €˜0 outcome:
=SUMPRODUCT(--('TB SAICMB'!B2:B1000="'Computer Sales"),--('TB SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="Tech"),'TB SAICMB'!E2:E1000)

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

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

Regards

EricB

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

Mike

"EricB" wrote:

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 Im 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" wrote:

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

"EricB" wrote:

The following formula is returning a €˜0 outcome:
=SUMPRODUCT(--('TB SAICMB'!B2:B1000="'Computer Sales"),--('TB SAICMB'!D2:D1000="Admin"),--('TB SAICMB'!D2:D1000="Tech"),'TB SAICMB'!E2:E1000)

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

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

Regards

EricB

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

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

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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

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

Ok, I see what you meant.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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