Sumproduct - Ignore errors

[Daxing]

Hi,
I hope the good people on here are able to help. Im tearing my hair out

I have a resource tracker which has a dashboard to pull data using a SUMPRODUCT from another sheet. If the data range on the sheet has a vlook which is blank, then the formula displays #N/A. This then causes the dashboard to fail as I cant get the formula to ignore errors. I am not suire if this can be done for sumproduct across another sheet.

There are two sheets:
CSS Dashboard which looks up data using sumproduct from CSS Demand sheet.
CSS Dashboard Formula =SUMPRODUCT(('CSS Demand'!$M$3:$M$1522=$B$12)*('CSS Demand'!$F$3:$F$1522=$C12)*('CSS Demand'!$R$3:$U$1522))
If CSS Demand sheet has a blank Vlookup formula, then it will display #N/A. I need the formula on the CSS Dashboad to ignore these errors.

Can it be done using a sumproduct? and can someone suggest a way of doing this, I have tried multiple ways using IFERROR, but I am stumped.

Thanks and Kind Regards,
Daxing

 

[joeu2004]

It would be easier to avoid the #N/A erorr in the cells where VLOOKUP fails. For example:

=IFERROR(VLOOKUP(...), "")

Otherwise, you will need to array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=SUM(IFERROR(IF('CSS Demand'!$M$3:$M$1522=$B$12,
IF('CSS Demand'!$F$3:$F$1522=$C12, IF('CSS Demand'!$R$3:$U$1522, 1, 0))), 0))

 

[Daxing]

Thank you so much for the resposne, I just tried the formula; however, it doesnt correctly return the values, the value using sum produt is 56 and the aboce formula returns 24; however, it ignoring errors. Ive checked the base data and 56 is the correct value it should be returning.
Is this possible due to a SUM being used and not SUMPRODUCT; hwoever, using SUMPRODUCT to replace SUM also brings back the wrong value.

 

[Daxing]

Ive just done the IFERROR on the VLOOKUP, you my friend are awsome

 

[joeu2004]

Sounds like you solved the problem using IFERROR(VLOOKUP(...),""). Great!

But for completeness, to answer your question about SUM v. SUMPRODUCT.... No, that should not be the root cause of the problem.

The usual benefit of using SUMPRODUCT instead of SUM is to avoid having to array-enter the formula.

Array-entering a formula is error-prone; if you forget and just press Enter, the formula returns a bogus value in some contexts, which you might overlook, instead of the usual #VALUE error.

But to avoid an error, we would need to array-enter a SUMPRODUCT formula anyway (not the same as yours, though).

So there is no benefit; we might as well use SUM.

-----

Aha! I misinterpreted your SUMPRODUCT formula. The array-entered SUM formula (press ctrl+shift+Enter instead of just Enter) should be:

=SUM(IFERROR(IF('CSS Demand'!$M$3:$M$1522=$B$12,
IF('CSS Demand'!$F$3:$F$1522=$C12, 'CSS Demand'!$R$3:$U$1522)), 0))

-----

Should the reference to $C12 be $C$12?

It might not make a difference. But it is good practice to be consistent.

 

[Aladin Akyurek]

@ Daxing

In which range(s) do you have errors?

 

[Daxing]

Joeu2004, =SUM(IFERROR(IF('CSS Demand'!$M$3:$M$1522=$B$12, IF('CSS Demand'!$F$3:$F$1522=$C12, 'CSS Demand'!$R$3:$U$1522)), 0)) works a treat

However, I've used IFERROR on the Vlookup and its made it easier to copy the formala

Thanks for sorting.

 

[Daxing]

Hi Aladin, All sorted, I have two methods from Joe2004 ehich have worked
=SUM(IFERROR(IF('CSS Demand'!$M$3:$M$1522=$B$12,
IF('CSS Demand'!$F$3:$F$1522=$C12, 'CSS Demand'!$R$3:$U$1522)), 0))

&

I used =IFERROR(VLOOKUP($D64,Resource!$A$2:$K$1005,4,FALSE),"")

which stopped the error being produced.



Cheers Everyone.

 

[Daxing]

Should the reference to $C12 be $C$12?

It might not make a difference. But it is good practice to be consistent.

BTW - Ive not used the $C$XX as I have a VB scripts which duplicates the line with all the formulas, and this way it allows the formula to automatically references the relevant cell for that row in column C, plus if I sort any columns, the formula will never be out of wack for the line it is on. I learnt the hardway that after I had sorted using $C$XX, my vlookup formula was calculating from a different row

Insert line VB ( I would credit this, but there was no tag left in the code to reference)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub