Using Range Names with a multiple IF Formula

[JimS]

Cannot figure how to make this formula work:


{=IF(Area="CAROLINAS",IF(Status="Completed",SUM(Margin/Revenue),"Wrong Answer"))}



The following are Range Names (all have the same number of rows):
Area
Status
Margin
Revenue

Its result is false – which is “Wrong Answer”, is should result in 14% (see the attachment).

When both the “IF” conditions match I need it to total the respective Margin and Revenue values and then divide the Margin Total by the Revenue Total.

Any help would be appreciated…

JimS

[Bob Phillips]

Try this array formula


=SUM(IF((Area="CAROLINAS")*(Status="Completed"),Margin/Revenue))

This is an array formula.
This means that you use Ctrl-Shift-Enter to commit the formula, not just Enter (array Enter it). Excel will put curly brackets around the formula in the formula bar, you don#t do this.
If you need to change the formula at any time, you must array Enter it again.

[JimS]

XLD,

Thanks for the response.

This is closer - but it is adding the results of the individual division equations (ie: after each Margin/Revenue has been calculated). So this formula results in "0.22" - which is "0.01" + "0.21". It's not adding all the Margins together to get a Margin Total and then add all the Revenues together to get a Revenue Total and then divide the Margin Total by the Reveune Total.

Any other ideas?

JimS

[Bob Phillips]

Try this then


=SUMPRODUCT(--(Area="CAROLINAS"),--(Status="Completed"),Margin)/SUMPRODUCT(--(Area="CAROLINAS"),--(Status="Completed"),Revenue)

[JimS]

XLD is the MAN...

Perfect - Thanks again...