SumProduct returns #DIV/0! when ranges include blank rows

[Naridox]

Current Formula: "=SUMPRODUCT(($H$7:$H$9)/($G$7:$G$9),(L7:L9))"

Column 1 (G7:G9) = Event Length
Column 2 (H7:H9) = Monetary Gain for event
Column 3 (L7:L9) = Hours worked by particular volunteer
Desired Calc = Total monetary contribution of each vol by hours worked

1. Formula works when ranges only include rows of actual data
2. Expand ranges beyond filled rows (so there are blank rows), #DIV/0! error

Examples: Col L works (ranges only on filled rows, Col M errors for extended range

 

[wudixin96]

=SUM(IFERROR($H$7:$H$10/$G$7:$G$10,0)*(M7:M10))


Array Formula

 

[Somendra Misra]

Hi,

See this file, just another approach.

Regards,

 

[Naridox]

@wudixin96 - Thanks for sending, but it didn't work. Still got #VALUE! (Ah, nevermind. Forgot to CSE. It works now. Thanks!)

@Somendra Misra - Thank you! This works, but what did you do, exactly?

 

[wudixin96]

@wudixin96 - Thanks for sending, but it didn't work. Still got #VALUE! (Ah, nevermind. Forgot to CSE. It works now. Thanks!)

@Somendra Misra - Thank you! This works, but what did you do, exactly?

I said it was a array formula ,not a normal formula.

 

[Somendra Misra]

@wudixin96 - Thanks for sending, but it didn't work. Still got #VALUE! (Ah, nevermind. Forgot to CSE. It works now. Thanks!)

@Somendra Misra - Thank you! This works, but what did you do, exactly?

I had created 3 named ranges which are dynamic in nature to use in SUMPRODUCT. Go through them in Name Manager by going to it by formula tab or by short cut key Ctrl+F3.

If you have any doubt in there than write back.

Regards,