Iferror and sumproduct together

[chef]

I have this formula that works perfect 99% time with drop down combo but have occasions when some depts showing a #div/0! on some columns
Ive tried an iferror in from but cant get to work or its me with the syntax

Any suggestions would be appreciated
regards
Ian


=SUMPRODUCT(--(Data!$E$2:$E$20000=Summary!$A$1),--(Data!$G$2:$G$20000=Summary!$D15),--(Data!$A$2:$A$20000=Summary!F$6),Data!$J$2:$J$20000)/$E15

 

[awoohaw]

can you post some sample data with xl2bb add in, please?

 

[awoohaw]

please fill in the light blue with representative sample of what data you are working with:

Book1
	C	D	E	F			I	J	K	L
1	Data A	Data E	Data G	Data J			E15	summary A1	Summary D1	Summary F6
2
3
4
5
6
7
8
Sheet2

 

[chef]

I'm sorry, but my IT dept have blocked this facility and I cant use and tried before.
Ive posted a small screenshot if this helps
In this example Allied/Medical/Nursing/Other Ther should be zero for this dept but these have values for another dept in drop down.
Im just tried to get rod of Div if possible and hope this makes sense.

Many thanks


D E F G H I

	Headcount	Under 30	Age 30-49	Age 50-59	Over 60
Operations Division	1654​	8.9%	31.3%	33.1%	26.7%
Administrative Services	98​	10.2%​	36.7%​	34.7%​	18.4%​
Allied Health Profession		#DIV/0!​	#DIV/0!​	#DIV/0!​	#DIV/0!​
Healthcare Sciences	117​
Medical And Dental		#DIV/0!​	#DIV/0!​	#DIV/0!​	#DIV/0!​
Nursing/Midwifery		#DIV/0!​	#DIV/0!​	#DIV/0!​	#DIV/0!​
Other Therapeutic
Support Services	1439​	8.8%​	30.9%​	32.5%​	27.9%​

 

[awoohaw]

Okay, so that is the report the formula is on.
can you copy and paste into a table the underlying columns in the Data Sheet and the Summary Sheets.
I don't see a cell identified here as E15 on the report sheet.

 

[awoohaw]

my suggestion would be to do this:

=IF(E15 = 0, "", 
=SUMPRODUCT(--(Data!$E$2:$E$20000=Summary!$A$1),--(Data!$G$2:$G$20000=Summary!$D15),--(Data!$A$2:$A$20000=Summary!F$6),Data!$J$2:$J$20000)/$E15
)

 

[chef]

many thanks and I can try a work around with that
At the moment that shows a circular reference now but I will set up a helper table and reference that

many thanks for your time and help
regards
Ian

 

[awoohaw]

I'mnot sure why that is creating a circular reference if it was not already. I did not add a new cell reference to the formula.

 

[awoohaw]

I'mnot sure why that is creating a circular reference if it was not already. I did not add a new cell reference to the formula.

unless Data!E2:E2000 are on the same worksheet as the E15 you reference.

Note: It is strongly discouraged by most excel experts (I am not an expert but I listen to them) to ever use sheet names in cell references on the sheet with that name.

 

[chef]

ok thats helpful advise as I have been defaulting to these sheet names all time
appreciate your input and help.
have a great weekend