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:G107

I have tried to nest AND in SUMIF and have failed with multiple tries.


See More: Excel: Nested IF/AND statements

Report •

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),"")



Report •

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 •

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



Report •

Related Solutions

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 •

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 •

Ask Question