Sumproduct issue

[nileshbhoyrub]

Hello,

Can someone help me with same...

I am using Sumproduct funtion which is as follows:

=SUMPRODUCT((F5:F19)*(E5:E19))/SUM(E5:E19)

It uses values from two colums (one is the weight and the other the values 0 and 1)

the formula work fine except if the Values column is BLANK.

How can I use the formula for it to omit the cells when it is blank ?

Thanks or your help.

 

[Eric W]

Welcome to the forum.

Try:

=SUMPRODUCT((F5:F19)*(E5:E19),--(F5:F19<>""))/SUMPRODUCT(E5:E19,--(F5:F19<>""))

 

[nileshbhoyrub]

The formula should calculate the compliance on an auditing sheet.
Weight Values
1 1
1 1
2 0
1
2 1
1 0


Compliance =SUMPRODUCT((F5:F19)*(E5:E19),--(F5:F19<>""))/SUMPRODUCT(E5:E19,--(F5:F19<>""))


Now the formula should only take into consideration the cells which contain the values 1 and 0.



If the cells values are empty, it should not decrease the compliance.

Help again for your help.

 

[Eric W]

Given that example, what should the result be?

 

[Aladin Akyurek]

Is this what you are after?

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(F5:F10),E5:E10)*F5:F10)/SUMIFS(E5:E10,F5:F10,"<>")

 

[nileshbhoyrub]

Hello Eric,

You were right, I made a mistake in doing the tests.

Sorry for that, Thanks again for your Precious Help.

Regards
Nilesh

 

[Eric W]

Ah, good! Glad we could help.