# Thread: SUMIFS, if 0 then = sheet1!C2 Thanks: 0 Likes: 0

1. ## SUMIFS, if 0 then = sheet1!C2

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

2. ## Re: SUMIFS, if 0 then = sheet1!C2

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

3. ## Re: SUMIFS, if 0 then = sheet1!C2

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)

4. ## Re: SUMIFS, if 0 then = sheet1!C2

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)

5. ## Re: SUMIFS, if 0 then = sheet1!C2

Originally Posted by pvr928
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

6. ## Re: SUMIFS, if 0 then = sheet1!C2

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

SETV

7. ## Re: SUMIFS, if 0 then = sheet1!C2

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

8. ## Re: SUMIFS, if 0 then = sheet1!C2

Originally Posted by SkatePropertyTofuEquities
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.

9. ## Re: SUMIFS, if 0 then = sheet1!C2

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