How to sum the totals on one column, if two other columns are true?

**cheesysocks** · 08-18-2008, 07:00 AM

Hi.

(Excel 2003 on windows XP.)

I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".

I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's. Could someone give me a pointer to the correct function please?

An example of some data...

Please Login or Register  to view this content.

Thanks in advance.

**StephenR** · 08-18-2008, 07:06 AM

This appears to work:

=SUMPRODUCT((C2:C5="Yes")*(COUNTIF(E2:I5,"w")>0)*(D2:D5))

**arthurbr** · 08-18-2008, 07:11 AM

Alternatively

=SUMPRODUCT((C2:C5="Yes")*(E2:I5="w")*D2:D5)

Will also work

There's a nice explanation on sumproduct here

**cheesysocks** · 08-18-2008, 01:54 PM

Originally Posted by arthurbr

There's a nice explanation on sumproduct here

Thanks. This is a real in depth piece of work and it'll take me ages to understand it, but I will! One day.

**cheesysocks** · 08-18-2008, 01:55 PM

And a big thanks for both the sumproduct formulas. Much apprecieated.

**arthurbr** · 08-18-2008, 02:42 PM

Thx for the feedback

**cheesysocks** · 08-26-2008, 07:16 AM

Originally Posted by arthurbr

Alternatively

Will also work

There's a nice explanation on sumproduct here

Hi again.

Sorry, but It doesn't work. Can anyone point me in the correct direction please? Attached is a sanatised real world worksheet. I have deleted numerous rows to reduce the filesize for posting. Normally it would go down to the 400 rows range.

The formula =SUMPRODUCT(($E$5:$E$400="Yes")*($N$5:$R$400="w")) works.

The formula =SUMPRODUCT(($E$5:$E$400="Yes")*($N$5:$R$400="w")*$J$5:$J$400) fails with a data type error. I think it's trying to multiply 'text' by 'numbers'. How can we get around this?

What I ultimately need is a formula in cell K154 that:

- looks at column E for a "yes" or "y"
- Looks in range N:R for a "w"
- If both are true then adds the number in column J (quantity of square meters cleaned) to a running total for the sheet.

It shouldn't be difficult, but there is no excel expert in my organisation to ask.

Again, thanks in advance for any tips.