SUMPRODUCT and IFERROR

[Ziro54]

Good day

Could someone please assist with including IFERROR into this formula. The displayed error is "#VALUE!".

=(SUMPRODUCT(('Data Sheet'!$A$4:$A$1000>=$F$10)*('Data Sheet'!$A$4:$A$1000<=$N$6)*('Data Sheet'!$BC$4:$BC$1000)))

IFERROR range BC4:BC1000. Thank you in advance.


Z

 

[AlanY]

this will displays ALL errors as blank, if this is what you're after

=iferror((SUMPRODUCT(('Data Sheet'!$A$4:$A$1000>=$F$10)*('Data Sheet'!$A$4:$A$1000<=$N$6)*('Data Sheet'!$BC$4:$BC$1000))),"")

 

[FormR]

IFERROR range BC4:BC1000.

Hi, this SUMIFS() alternative will ignore errors in the sumrange.

=SUMIFS('data sheet'!$BC$4:$BC$1000,'data sheet'!$BC$4:$BC$1000,"<=9.99E+307",'data sheet'!$A$4:$A$1000,">="&$F$10,'data sheet'!$A$4:$A$1000,"<="&$N$6)

 

[Ziro54]

Hi, this SUMIFS() alternative will ignore errors in the sumrange.

=SUMIFS('data sheet'!$BC$4:$BC$1000,'data sheet'!$BC$4:$BC$1000,"<=9.99E+307",'data sheet'!$A$4:$A$1000,">="&$F$10,'data sheet'!$A$4:$A$1000,"<="&$N$6)

Thank you, that seem to work. Any chance you can help this noob with one last one?

=(SUMPRODUCT((MONTH('Data Sheet'!$A$4:$A$1000)=MONTH($N$6))*('Data Sheet'!$BC$4:$BC$1000)))

 

[FormR]

If you have multiple years in A4:A1000 and want to sum all dates with the same month regardless of the year then we will need a different approach. But if you only have one year or only want to sum the dates that have the same month and year as the date in N6 then you can try:

=SUMIFS('Data Sheet'!$BC$4:$BC$1000,'Data Sheet'!$BC$4:$BC$1000,"<=9.99E+307",'Data Sheet'!$A$4:$A$1000,">="&EOMONTH($N$6,-1)+1,'Data Sheet'!$A$4:$A$1000,"<"&EOMONTH($N$6,0)+1)

 

[Ziro54]

If you have multiple years in A4:A1000 and want to sum all dates with the same month regardless of the year then we will need a different approach. But if you only have one year or only want to sum the dates that have the same month and year as the date in N6 then you can try:

=SUMIFS('Data Sheet'!$BC$4:$BC$1000,'Data Sheet'!$BC$4:$BC$1000,"<=9.99E+307",'Data Sheet'!$A$4:$A$1000,">="&EOMONTH($N$6,-1)+1,'Data Sheet'!$A$4:$A$1000,"<"&EOMONTH($N$6,0)+1)

Thank you for all the help! Currently only using one year.

My SUMPRODUCT works fine, it is only cause I am being difficult. The cell Range BC4:BC1000 normally only have a sum forumla. Although I don't like seeing a bunch on "0's" in the data sheet. I created a IF formula.

=IF(B84>0,(SUM(AX84+BA84)/8.8),"")

Only recently started using fomulas in Excel. Probably better ways of doing it...

Once again, thank you for the help!

 

[Ziro54]

[SOLVED] SUMPRODUCT and IFERROR

Solved