Ignoring blanks using a Conditional SUMPRODUCT formula

[winemakerl]

[TABLE="width: 142"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
Hello everyone,
Thanks in advance for your help, thanks also for all the previous solutions I've found.
I am using a SUMPRODUCT formula with conditions to determine weighted averages of analyses against volumes. My issue is that not all analyses have been entered, and my formula is including blanks as 0, rather than ignoring.
Here is my formula:
=SUMPRODUCT('J&S'!N2:N1000*'J&S'!E2:E1000*('J&S'!B2:B1000="WRHS"))/SUMIF('J&S'!B2:B1000,"WRHS",'J&S'!E2:E1000)

column N are my analyses
column E are my volumes
column B are my conditions, in this example "WRHS"

I have several blanks in column N for analyses that have not yet been determined.

Any advice would be appreciated, please let me know if I have not included enough information or described my spreadsheet appropriately.
Cheers, L

 

[Tetra201]

See if this works for you:

=SUMPRODUCT(('J&S'!N2:N1000)*('J&S'!E2:E1000)*('J&S'!B2:B1000="WRHS"))/SUMPRODUCT(('J&S'!B2:B1000="WRHS")*('J&S'!E2:E1000)*ISNUMBER('J&S'!N2:N1000))

 

[Aladin Akyurek]

A weighted average calculation would be a tad faster using SUMPRODUCT in native syntax...

=SUMPRODUCT(--ISNUMBER('J&S'!N2:N1000),'J&S'!N2:N1000,'J&S'!E2:E1000,--('J&S'!B2:B1000="WRHS"))/SUMIFS('J&S'!E2:E1000,'J&S'!B2:B1000,"WRHS",'J&S'!N2:N1000,"<>")

 

[winemakerl]

See if this works for you:

=SUMPRODUCT(('J&S'!N2:N1000)*('J&S'!E2:E1000)*('J&S'!B2:B1000="WRHS"))/SUMPRODUCT(('J&S'!B2:B1000="WRHS")*('J&S'!E2:E1000)*ISNUMBER('J&S'!N2:N1000))

Brilliant! Thanks

 

[Tetra201]

You are welcome.