Home 
Search 
Today's Posts 
#1




nested sumif or sumif with two criteria
Say for example I have three columns; the first is a name column, second is
color and third is cost. I am trying to create a formula that will sum all cells in the third column (cost) that matches the criteria of the name "Bob" in column A AND "Red" in column B. I know I am close and I know I can use the "*" somehow but I can't figure out the syntax. Any help is appreciated. 
#2




Try
=sumproduct((A2:A100="Bob"),(B2:B100="Red"),C2:C100) "dshigley" wrote: Say for example I have three columns; the first is a name column, second is color and third is cost. I am trying to create a formula that will sum all cells in the third column (cost) that matches the criteria of the name "Bob" in column A AND "Red" in column B. I know I am close and I know I can use the "*" somehow but I can't figure out the syntax. Any help is appreciated. 
#3




I did a search on my question before I posted and one of last years answers
was the exact same thing. I tried that formula but cannot seem to get it to work. Thanks though. Dan "Duke Carey" wrote: Try =sumproduct((A2:A100="Bob"),(B2:B100="Red"),C2:C100) "dshigley" wrote: Say for example I have three columns; the first is a name column, second is color and third is cost. I am trying to create a formula that will sum all cells in the third column (cost) that matches the criteria of the name "Bob" in column A AND "Red" in column B. I know I am close and I know I can use the "*" somehow but I can't figure out the syntax. Any help is appreciated. 
#4




"dshigley" wrote in message
... Say for example I have three columns; the first is a name column, second is color and third is cost. I am trying to create a formula that will sum all cells in the third column (cost) that matches the criteria of the name "Bob" in column A AND "Red" in column B. I know I am close and I know I can use the "*" somehow but I can't figure out the syntax. Any help is appreciated. In my experience, if you want to sum based on 2 or more criteria you have to use DSUM. SUMIF works with only 1 criterion.  Peter Aitken Remove the crap from my email address before using. 
#5




Duke's equationm should have worked
put in a temporary column and use =if(a2="Bob",1,2) and copy down to row 100 See3 if all the "Bob" come up to check if you might have extra spaces or something. do likewise with "Red" "dshigley" wrote: I did a search on my question before I posted and one of last years answers was the exact same thing. I tried that formula but cannot seem to get it to work. Thanks though. Dan "Duke Carey" wrote: Try =sumproduct((A2:A100="Bob"),(B2:B100="Red"),C2:C100) "dshigley" wrote: Say for example I have three columns; the first is a name column, second is color and third is cost. I am trying to create a formula that will sum all cells in the third column (cost) that matches the criteria of the name "Bob" in column A AND "Red" in column B. I know I am close and I know I can use the "*" somehow but I can't figure out the syntax. Any help is appreciated. 
#6




"dshigley" wrote...
I did a search on my question before I posted and one of last years answers was the exact same thing. I tried that formula but cannot seem to get it to work. Thanks though. .... In what way does the formula not work? It does work if you've given correct specs and have ensured your data range contains no leading or trailing spaces in the entries in columns A and B and all entries are numbers rather than text looking like numbers in column C. "Duke Carey" wrote: Try =sumproduct((A2:A100="Bob"),(B2:B100="Red"),C2:C100) .... Basic formula debugging. What do the formulas =COUNTIF(A2:A100,"Bob") =SUMPRODUCT((TRIM(A2:A100)="Bob")) =SUMPRODUCT((TRIM(SUBSTITUTE(A2:A100,CHAR(168),""))="Bob")) =COUNTIF(B2:B100,"Red") =SUMPRODUCT((TRIM(B2:B100)="Red")) =SUMPRODUCT((TRIM(SUBSTITUTE(B2:B100,CHAR(168),""))="Red")) =SUM(C2:C100) =SUMPRODUCT(C2:C100) return? If the first 3 and/or second 3 return different results, you have either normal ASCII or nonbreaking HTML spaces in your columns A and/or B. If the last two formulas return different results, some or all of your column C values are text. The most robust formula would be =SUMPRODUCT((TRIM(SUBSTITUTE(A2:A100,CHAR(168),""))="Bob"), (TRIM(SUBSTITUTE(B2:B100,CHAR(168),""))="Red"),C2:C100) However, the best approach would be to clean up the data in A2:C100. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I use the sumif if I have multiple criteria (i.e. greater .  Excel Worksheet Functions  
SUMIF using VLOOKUP as criteria  Excel Worksheet Functions  
"criteria" in a sumif refering to the value in another cell  Excel Discussion (Misc queries)  
How do I ask for multiple criteria when creating a "sumif" formul.  Excel Worksheet Functions  
Can I use a cell reference in the criteria for the sumif function.  Excel Worksheet Functions 