SUMIFS, if 0 then = sheet1!C2

Joined
Jan 24, 2018
Messages
5
Hey all,

I need excel to try to find data meeting 2 criteria and if the data does not meet them, to default to the amount in sheet1!C2

I currently have this working perfectly:
=SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)

Which says: sumrange (Journal!b4:b1000), if A is found in CriteriaRange1, and B is above (a date)-1 but below (another date) then return the corresponding figure in the sumrange. But it returns a value of "0" if criteria not met - as i said, I need it to default to the value provided in sheet1!C2

Thank you!
SPTE
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi SPTE

Does this do what you want:

Code:
=IF(SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)=0,
Sheet1!C2,
SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6))

Cheers

pvr928
 
Upvote 0
Something like

=IF(COUNTIFS(Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6),
SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6),S
heet1!C2)
<strike></strike><strike></strike>
 
Upvote 0
If Microsoft would ever come to terms with SETV/GETV (Longre), we could have:

=IF(SETV(SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)),GETV(),Sheet1!C2)
 
Last edited:
Upvote 0
Hi SPTE

Does this do what you want:

Code:
=IF(SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)=0,
Sheet1!C2,
SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6))

Cheers

pvr928

______________________________________________________________________________________________________________________________________________

Ahh well done! Because the issue occurred after i had input the formula, I predisposed my brain to thinking the solution would have to go immediatly after the existing formula e.g. =sumif(etc.etc)OR(etc.etc). But your solution was to consider the issue first hence the IF.... Thank you very much! Interesting physiology behind it too


SPTE
 
Upvote 0
That window appears because it cannot find one or more of the worksheet references in the formula in the current workbook - ie Excel is asking you to point to the workbook which does contain the missing worksheet/s.

Consequently, check that:

Journal
Cashflow
Sheet1

all appear in the current workbook (I assume that they are meant to, and that they are not intended to instead exist in a separate workbook).

Cheers

pvr928
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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