Ziro54

New Member
Joined
Aug 3, 2014
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Good day

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

Code:
=(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
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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))),"")
 
Upvote 0
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)
 
Upvote 0
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?

Code:
=(SUMPRODUCT((MONTH('Data Sheet'!$A$4:$A$1000)=MONTH($N$6))*('Data Sheet'!$BC$4:$BC$1000)))
 
Upvote 0
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:

Code:
=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)
 
Last edited:
Upvote 0
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:

Code:
=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.

Code:
=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!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top