trouble with #REF! error

[Mark Arthur]

This is my formula but if any cell in range c5-c41 is blank it returns a ref error

=IF(SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")))=0,"",SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55"))))

 

[steve the fish]

Dont use a fixed range like Settings!C5:C41. Make that into a named range and dont put blank cells in it or it will error.

 

[AlanY]

deleted..

 

[Mark Arthur]

The range is a list of names. I need the list to be able to be added to. This the range. There are 37 blank cells in the range and I need to be able to use whatever names are in the range.

 

[steve the fish]

I would be fixing my spaces but you could use this formula but it leaves you open to not seeing other errors which may be in the data and producing results that are not correct. If you are sure your data is sound and there can be no errors other than the spaces causing them then:

=IF(SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")),0))=0,"",SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")),0)))

Enter CTRL-SHIFT-ENTER.

 

[Mark Arthur]

Will try this and report back. Ty

 

[Mark Arthur]

did not work, this formula only reported blanks. I am no excel guru this is why I'm posting here, I'm self taught and I'm not a good teacher. lol

what I have is a Race result and standings spreadsheet that has to pull data from multiple tabs. each tab representing a race. the race list could be anywhere from 1-37 races. this is where the blanks come into play. I need the area people can enter races to be 37 spaces but i need the formula to be able to see each race added. I would be happy to send the file to anyone willing to help. the workbook is complete all but this problem

 

[jmartin2178]

This is my formula but if any cell in range c5-c41 is blank it returns a ref error

=IF(SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")))=0,"",SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55"))))

Give this a try:

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")))=0,"",SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")))),0)

 

[Mark Arthur]

might have it sorted by removing the =IF function will report back

 

[Mark Arthur]

TY for you help, got it working with this formula

{=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B2,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")),0))}