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

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History