Link to home
Start Free TrialLog in
Avatar of kgerb
kgerbFlag for United States of America

asked on

SUMPRODUCT with Non-Contiguous Range

Hello All,
I've been struggling with this for quite a while now and I can't seem to get it right.  Here is what I need to do:

=SUMPRODUCT((K11,O11,S11,W11,AA11,AE11,AI11,AM11,AQ11)="R",(H11,L11,P11,T11,X11,AB11,AF11,AJ11,AN11))

I realize this doesn't work, but you get the idea.  Check whether the cells K11, O11, S11, ... are equal to "R" and create an array with 1's and 0's.  Multiply this array with another array created from the values of the cells H11, L11, P11, ... and then sum the result.

I tried the following and I think it should work but it's not and I don't know why.  I don't really like this approach b/c the cell references are static strings and I can't drag the formula down but if I have to use something like this I will.

=SUMPRODUCT(INDIRECT({"K11","O11","S11","W11","AA11","AE11","AI11","AM11","AQ11"})="R",INDIRECT({"H11","L11","P11","T11","X11","AB11","AF11","AJ11","AN11"}))

Thanks in advance for the help.

Kyle
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kgerb

ASKER

I'm at a loss for words...brilliant...I don't know what else to say...simply brilliant

Thanks Rory

Kyle
Glad to help. :)