# SUMPRODUCT help needed to calculate rate (requires brain of a genius)

1. ## SUMPRODUCT help needed to calculate rate (requires brain of a genius)

This requires brain of a genius to solve. I need a formula for column L, so that I can get rid of the manual helper columns of D and E.

Thank you so very much.  Register To Reply

2. ## Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

Perhaps this array formula:

=SUMPRODUCT(IF(\$A\$2:\$A\$19=\$A\$1:\$A\$18,IF(\$A\$2:\$A\$19=\$A\$3:\$A\$20,\$B\$1:\$B\$18-\$B\$2:\$B\$19,\$B\$1:\$B\$18-I3),J3-\$B\$2:\$B\$19)*(\$A\$2:\$A\$19=H3)*(\$C\$2:\$C\$19))  Register To Reply

3. ## Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

similar variant, also requiring array entry:

L3:
=SUM(TEXT((IF((\$B\$1:\$B\$18<\$J3)*(\$A\$1:\$A\$18=\$H3),\$B\$1:\$B\$18,\$J3)-IF(\$B\$2:\$B\$19>\$I3,\$B\$2:\$B\$19,\$I3))*(\$A\$2:\$A\$19=\$H3),"0;\0;0;")*(\$C\$2:\$C\$19))
confirmed with CTRL + SHIFT + ENTER
copied down to L4 etc  Register To Reply

4. ## Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

SUMPRODUCT with CSE makes me want to scream...  Register To Reply

5. ## Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

ARRAY formula in M2 then copy down ``Please Login or Register  to view this content.``
How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.  Register To Reply

6. ## Re: SUMPRODUCT help needed to calculate rate (requires brain of a genius)

words cannot express how grateful to all of you.

You guys are great! indeed genius

Thank you so very much!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 