DKRbella0814
Board Regular
- Joined
- Aug 10, 2008
- Messages
- 155
I need some help reviewing the formula I've written to understand why the #VALUE error is returning. The formula is intended to sum all bank charges which meet certain criteria, including: category of spend (i.e. food, gas, etc.), spend month and year (i.e. Jan-20, Feb-20), and whether the charge is from a certain person (i.e. "Danny") since there is one joint account (i.e. Bank2)
The original formula is as follows, which returns #VALUE error:
=-(SUMIFS('Bank1'!$Q$252:$Q$347,'Bank1'!$R$252:$R$347,'Budget-20'!$B23,'Bank1'!$S$252:$S$347,'Budget-20'!$F$4)+SUMIFS(Bank2!$L$1738:$L$1745,Bank2!$M$1738:$M$1745,'Budget-20'!$B28,Bank2!$N$1738:$N$1745,"Danny",Bank2!$O$1740:$O$1745,'Budget-20'!$F$4)); where
Budget-20$B23 = Food (spend category)
Budget-20$f$4 = text version of date and year
Note: Bank1: $S$252:$S$347 and Bank2 $O$1740:$O$1745 both store the date as Text to properly cross-reference with 'Budget-20'$f$4
I tried troubleshooting the error by separating the two formulas. When I used SUMIFS for Bank1 charges, the formula works properly. Therefore, I know that the issue is with Bank2 formula. I am not sure whether this is because of use of 3 criteria and whether the criteria has been written properly.
Please help!
The original formula is as follows, which returns #VALUE error:
=-(SUMIFS('Bank1'!$Q$252:$Q$347,'Bank1'!$R$252:$R$347,'Budget-20'!$B23,'Bank1'!$S$252:$S$347,'Budget-20'!$F$4)+SUMIFS(Bank2!$L$1738:$L$1745,Bank2!$M$1738:$M$1745,'Budget-20'!$B28,Bank2!$N$1738:$N$1745,"Danny",Bank2!$O$1740:$O$1745,'Budget-20'!$F$4)); where
Budget-20$B23 = Food (spend category)
Budget-20$f$4 = text version of date and year
Note: Bank1: $S$252:$S$347 and Bank2 $O$1740:$O$1745 both store the date as Text to properly cross-reference with 'Budget-20'$f$4
I tried troubleshooting the error by separating the two formulas. When I used SUMIFS for Bank1 charges, the formula works properly. Therefore, I know that the issue is with Bank2 formula. I am not sure whether this is because of use of 3 criteria and whether the criteria has been written properly.
Please help!