SUMIFS, if 0 then = sheet1!C2

[SkatePropertyTofuEquities]

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

 

[pvr928]

Hi SPTE

Does this do what you want:

=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

 

[Aladin Akyurek]

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>

 

[Aladin Akyurek]

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)
​

 

[SkatePropertyTofuEquities]

Hi SPTE

Does this do what you want:

=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

 

[SkatePropertyTofuEquities]

I have no idea what SETV and GETV but interested if you care to explain =)

SETV

 

[SkatePropertyTofuEquities]

Again thank you for your help. Now it keeps asking me to update my values in a pop up window... any ideas?

 

[Aladin Akyurek]

Again thank you for your help. Now it keeps asking me to update my values in a pop up window... any ideas?

Post #3 uses COUNTIFS than SUMIFS if appropriate, otherwise returns Sheet1!C2.

​

Post #4 is a hint for Microsoft, not for you.

 

[pvr928]

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