LinkBack URL
About LinkBacks
Does anyone know of a way to clean up the following possibly using the OR function embedded in SUMPRODUCT. I have tried various connotation with no success.
Please Login or Register to view this content.
Does anyone know of a way to clean up the following possibly using the OR function embedded in SUMPRODUCT. I have tried various connotation with no success.
Please Login or Register to view this content.
Last edited by BobTheRocker; 06-28-2016 at 05:17 AM.
Try
=IF($G278="","",SUMPRODUCT(--($B$6:$B$252=$G278)*((CU$6:CU$252=$C278)+(CU$6:CU$252=$D278)+(CU$6:CU$252=$E278))))
OR condition is implemented using "+"
AND condition using "*"
One way...
=IF($G278="","",SUMPRODUCT(--($B$6:$B$252=$G278),--ISNUMBER(MATCH(CU$6:CU$252,$C278:$E278,0))))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try
=IF($G278="","",SUMPRODUCT(--($B$6:$B$252=$G278)*(COUNTIF($C278:$E278,CU$6:CU$252))))
That syntax is very inefficient on large amounts of data.Originally Posted by kvsrinivasamurthy
The ISNUMBER(MATCH syntax is more than twice as fast to calculate.
Thanks Tony, this seems to work very well.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks