SUMPRODUCT across several columns

**BobTheRocker** · 06-12-2011, 07:18 AM

I have used this code many times to look at a range of cells in column CE containing the word READING and return the total value of the number contained in corresponding rows in column CW that have a greater value than one.

=SUMPRODUCT(--($CE$6:$CE$45="READING")*(CW$117:CW$156>1), CW$117:CW$156)

The problem is that the word reading could appear across several columns but I still only want to evaluate values contained in column CW. I thought the following would work but it returns #VALUE! error.

=SUMPRODUCT(--($CE$6:$CL$45="READING")*(CW$117:CW$156>1), CW$117:CW$156)

Any advice appreciated

**Roger Govier** · 06-12-2011, 08:41 AM

Hi

Because your first range is larger than the other 2 try

=SUMPRODUCT(($CE$6:$CL$45="READING")*(CW$117:CW$156>1)*CW$117:CW$156)

**BobTheRocker** · 06-12-2011, 08:50 AM

Thank you, but I can't get that to work, I think it is because SUMPRODUCT is limited to the number of cells it can compute/analyse but I can't figure out an alternative at the moment.

**BobTheRocker** · 06-12-2011, 08:55 AM

=SUMPRODUCT(--($CE$6:$CL$45="READING")*(CV$117:CV$156>1))

This formula works but only returns the number of cells in CV117 to CV156 that are greater than 1 with a corresponding "READING" in columns CE to CL. I need to SUM these values. It seems to be counting the occurrence of these values rather than summing them.

**daddylonglegs** · 06-12-2011, 09:41 AM

Roger's suggestion works for me - what result do you get? Any (non-coercable) text in the range CV$117:CV$156 will result in an error (#VALUE!)......one way round that is to use an "array formula", i.e.

=SUM(IF(($CE$6:$CL$45="READING")*(CW$117:CW$156>1),CW$117:CW$156))

confirmed with CTRL+SHIFT+ENTER

Note that both that version and Roger's will sum the same row more than once if "Reading" exists more than once in the same row......is that possible?

**BobTheRocker** · 06-12-2011, 09:53 AM

Thank you daddylonglegs, your array formula works. Though I don't understand why Roger's didn't. The CV column only contains numbers to 5 decimal places but as you say it returns #VALUE!. What do you mean by "Any (non-coercable) text".

Thank you both for your input. I would really prefer to use Roger's non array formula but fail to understand why I can't.

**BobTheRocker** · 06-12-2011, 09:56 AM

Strange, I just formatted the range of cells to text and back to number (5 decimal points) again and now Roger's formula works. Is it possible I had a cell in the range CV117:CV156 that was formatted as text, would this return #VALUE!

**Roger Govier** · 06-12-2011, 06:05 PM

Originally Posted by BobTheRocker

Thank you daddylonglegs, your array formula works. Though I don't understand why Roger's didn't. The CV column only contains numbers to 5 decimal places but as you say it returns #VALUE!. What do you mean by "Any (non-coercable) text".

Thank you both for your input. I would really prefer to use Roger's non array formula but fail to understand why I can't.

Bob
Do you want to post a sample of your data, then maybe we can see why it doesn't work for you.
Works fine for me (and for ddl) as does the ddl Array formula.