|
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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". |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
SUMIF or SUMPRODUCT?
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 |
| All times are GMT +1. The time now is 07:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com