If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct excluding multiple criteria
Hi,
I'm trying to use the SUMPRODUCT formula to add up a colunm but exlude certain values based on a criteria in another column. I have column A which has the criteria and colunm H which has the value. I want to add up all of colunm H but exclude certain values which meet a specific criteria. For example, add up column H except for the values under column A that is equal to the criteria 3420 and 4474: A H 1 3410 $100 2 3420 $200 3 4474 $300 4 5425 $400 Thanks, |
#2
|
|||
|
|||
Sumproduct excluding multiple criteria
Try this:
C1 = 3420 C2 = 4474 =SUMPRODUCT(--(ISNA(MATCH(A1:A4,C1:C2,0))),H1:H4) Or, with the variables hardcoded in the formula: =SUMPRODUCT(--(ISNA(MATCH(A1:A4,{3420;4474},0))),H1:H4) -- Biff Microsoft Excel MVP "Ivano" wrote in message ... Hi, I'm trying to use the SUMPRODUCT formula to add up a colunm but exlude certain values based on a criteria in another column. I have column A which has the criteria and colunm H which has the value. I want to add up all of colunm H but exclude certain values which meet a specific criteria. For example, add up column H except for the values under column A that is equal to the criteria 3420 and 4474: A H 1 3410 $100 2 3420 $200 3 4474 $300 4 5425 $400 Thanks, |
#3
|
|||
|
|||
Sumproduct excluding multiple criteria
either
=SUMPRODUCT(--(A1:A43420),--(A1:A44474),B1:B4) or =SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4)) Adjust ranges as needed. Note that only Excel 2007 permits full column references with SUMPRODUCT best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ivano" wrote in message ... Hi, I'm trying to use the SUMPRODUCT formula to add up a colunm but exlude certain values based on a criteria in another column. I have column A which has the criteria and colunm H which has the value. I want to add up all of colunm H but exclude certain values which meet a specific criteria. For example, add up column H except for the values under column A that is equal to the criteria 3420 and 4474: A H 1 3410 $100 2 3420 $200 3 4474 $300 4 5425 $400 Thanks, |
#4
|
|||
|
|||
Sumproduct excluding multiple criteria
=SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4))
simplify version: =SUM(B11:B4,-SUMIF(A1:A4,{3420,4474},B1:B4)) "Bernard Liengme" wrote: either =SUMPRODUCT(--(A1:A43420),--(A1:A44474),B1:B4) or =SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4)) Adjust ranges as needed. Note that only Excel 2007 permits full column references with SUMPRODUCT best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ivano" wrote in message ... Hi, I'm trying to use the SUMPRODUCT formula to add up a colunm but exlude certain values based on a criteria in another column. I have column A which has the criteria and colunm H which has the value. I want to add up all of colunm H but exclude certain values which meet a specific criteria. For example, add up column H except for the values under column A that is equal to the criteria 3420 and 4474: A H 1 3410 $100 2 3420 $200 3 4474 $300 4 5425 $400 Thanks, |
#5
|
|||
|
|||
Sumproduct excluding multiple criteria
I always forget that { } stuff !!!
Thanks But I prefer the logic of =SUM(B11:B4) - SUMIF(A1:A4,{3420,4474},B1:B4) cheers -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Teethless mama" wrote in message ... =SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4)) simplify version: =SUM(B11:B4,-SUMIF(A1:A4,{3420,4474},B1:B4)) "Bernard Liengme" wrote: either =SUMPRODUCT(--(A1:A43420),--(A1:A44474),B1:B4) or =SUM(B1:B4)-(SUMIF(A1:A4,3420,B1:B4)+SUMIF(A1:A4,4474,B1:B4)) Adjust ranges as needed. Note that only Excel 2007 permits full column references with SUMPRODUCT best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ivano" wrote in message ... Hi, I'm trying to use the SUMPRODUCT formula to add up a colunm but exlude certain values based on a criteria in another column. I have column A which has the criteria and colunm H which has the value. I want to add up all of colunm H but exclude certain values which meet a specific criteria. For example, add up column H except for the values under column A that is equal to the criteria 3420 and 4474: A H 1 3410 $100 2 3420 $200 3 4474 $300 4 5425 $400 Thanks, |
Thread Tools | |
Display Modes | |
|
|