[SOLVED] Simplifying a Sumproduct formula

BobTheRockeer · #1 02-08-2010, 09:27 AM

I'm using Excel 2003. I have been using SUMPRODUCT to look at too compare columns and data that is contained in both.

Column L11:L52 contains the numbers 1 to 4 and Column K11:K52 contains the word YES in some cells. I just want to look and see if text is contained in a row in column L is it contained in that row in column K. This is the formula I'm using but it seems over complicated and I'm sure there is a way of just looking if the cell contains information without worrying what it is.

=SUMPRODUCT(--(Data!$L$11:$L$52=1),--(Data!$K$11:$K$52="YES"))+SUMPRODUCT(--(Data!$L$11:$L$52=2),--(Data!$K$11:$K$52="YES"))+SUMPRODUCT(--(Data!$L$11:$L$52=3),--(Data!$K$11:$K$52="YES"))+SUMPRODUCT(--(Data!$L$11:$L$52=4),--(Data!$K$11:$K$52="YES"))

Any help gratefully recieved, I'm something of an amateur at this.

rwgrietveld · #2 02-08-2010, 09:34 AM

This Array function would work

Code:

=SUM(--(K11:K52<>"")*(L11:L52<>""))

BobTheRockeer · #3 02-08-2010, 09:51 AM

=SUM(--(K11:K52<>"")*(L11:L52<>""))

The array formula above fixed it but could not get the other to work.

Thank you very much!

DonkeyOte · #4 02-08-2010, 09:52 AM

You can of course still use SUMPRODUCT if you prefer ?

=SUMPRODUCT(--ISNUMBER($L$11:$L$52),--($K$11:$K$52="Yes"))

or if you wish to restrict to just 1 to 4

=SUMPRODUCT(--ISNUMBER(MATCH($L$11:$L$52,{1,2,3,4},0)),--($K$11:$K$52="Yes"))

If you want to avoid SUMPRODUCT and Arrays altogether simply use concatenation in another column, eg:

M11: =L11&"@"&K11
copied down

Then

=SUM(COUNTIF($M$11:$M$52,{1,2,3,4}&"@Yes"))

Welcome to the Excel Forum

Forum Statistics