Microsoft Excel 2003 (full product)

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.

Thanks

Keri

There are probably other ways, but try this: =IF(AND(COUNTIF(C5:C107,"salary"),COUNTIF(F5:F107,"v" )),SUM(G5:G107),"")

MIKE

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

andthe location of the range you are summing. They are in different places for the 2 functions.

=SUMIFS(G5:G107, C5:C107, "=salary", F5:F107, "=v")Need to load 2007 so I can play with all the new toys......

MIKE

I just loaded 2010 and tested the SUMIFS solution. It passed.

BTW, In typical MS fashion, the load of 2010 has its quirks.

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!

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History