Thread: Simplifying a Sumproduct formula

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.

This Array function would work

Code:

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

Press Ctrl+Shift+Enter

Alternative

Code:

=SUM(NOT(ISBLANK(K11:K52))*NOT(ISBLANK(L11:L52)))

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"))