# Excel: Nested IF/AND statements

Microsoft Excel 2003 (full product)
September 15, 2010 at 12:43:42
Specs: Windows XP
 I need to sum a column IF two criteria are met.Basically, I need excel to look at column C to see if a cell holds "salary" AND column F to see if a cell holds "V". If both conditions are met I need the amount in column G to SUM.IF C5:C107="salary" AND F5:F107="v" THEN SUM G5:G107I have tried to nest AND in SUMIF and have failed with multiple tries.ThanksKeri

See More: Excel: Nested IF/AND statements

#1
September 15, 2010 at 15:12:02
 There are probably other ways, but try this:=IF(AND(COUNTIF(C5:C107,"salary"),COUNTIF(F5:F107,"v" )),SUM(G5:G107),"")MIKEhttp://www.skeptic.com/

Report •

#2
September 15, 2010 at 16:14:18
 I believe that this is why SUMPRODUCT was invented! ;-)=SUMPRODUCT((C5:C107="salary")*(F5:F107="v")*(G5:G107)) [Tested]If you are running 2007 or above, I believe you can use SUMIFS=SUMIFS(G5:G107, C5:C107, "=salary", F5:F107, "=v") [Untested]Note the location of the double quotes and the location of the range you are summing. They are in different places for the 2 functions.

Report •

#3
September 15, 2010 at 20:27:39
 =SUMIFS(G5:G107, C5:C107, "=salary", F5:F107, "=v")Need to load 2007 so I can play with all the new toys......MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
September 16, 2010 at 00:52:44
 I just loaded 2010 and tested the SUMIFS solution.It passed.BTW, In typical MS fashion, the load of 2010 has its quirks.

Report •

#5
September 16, 2010 at 07:56:45
 Thanks Alot!!SUMPRODUCT worked like a charm!Unfortunately I do not have 2007 to use SUMIFS. I'll have to discuss this with my employer!Again, thank you for your responses!

Report •