SUMIFS formula returning error

[DKRbella0814]

Hello,

What I would like is for a formula to return a sum for a defined range which meets certain criteria (via cell references from a different worksheet). Currently, the formula is returning 0 and I have not been successful in troubleshooting (checked range to ensure this was consistent, checked consistency in formatting for referenced dates - which I have programmed as text, etc.).

In my workbook, there are several tabs which represent transactions from different financial institutions. Each tab is structured the same, so if I can figure out the correct formula for one tab, I should be able to adapt for all other tabs in the workbook. Also, within the workbook is a 'Budget' tab; on this tab is where I would like to write the formula. To simplify things a bit, the 'Budget' tab has various categories of income and spend (i.e. income, bonus, gas, food, house, etc.) by month for the year (i.e. Jan-20, Feb-20, etc.). The months have been formatted as text. Since there are multiple financial institutions which may have income/spend for the same category referenced on 'Budget' tab, I will need to SUM multiple SUMIFS functions, if possible.

A simplified version of the workbook is below:

'Budget' - contains formula which should add SUMIFS functions across all banking tabs
'Bank1' - contains income/spend for one financial institution (itemizes each transaction by date; the date is summarized as MMM-YY (i.e. Jan-20) as text)
Formula (written on 'Budget' tab): =SUMIFS('Bank1'!$Q$251:$Q$302,'Bank1'!$R$251:$R$302,$B7,'Bank1'!$S$251:$S$302,$L$4) where:

$Q$251:$Q$302 = sum range or $ amount for each income/expense line item: ('Bank1' tab)
$R$251:$R$302 = category of income/expense (i.e. Bonus, Gas, Food): ('Bank1' tab)
$S$251:$S$302 = MMM-YY (format = text), $B7 = "Bonus" ;and ('Bank1' tab)
$L$4 = "Mar-20" (both on 'Budget' tab, where formula is written)

Please help!

 

[DanteAmor]

If in this range ($S$251:$S$302) you have dates, then you should compare against another date.
In $L$4 do you have a date?

 

[DKRbella0814]

Hi!
Yes, the dates in above referenced range have been normalized to text mmm-yy so that when indexed to $L$4 it is also text with same format.

 

[DKRbella0814]

Hi!
Yes, the dates in above referenced range have been normalized to text mmm-yy so that when indexed to $L$4 it is also text with same format.

I’m very confused by this because it should be apples to apples

 

[DanteAmor]

If you have a date in the cell, it will remain a date even if you change the format of the cell.

So to help you, write here, exactly the value of the formula bar.

Or upload a file with a sample of data, to the cloud to review it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[DKRbella0814]

I figured it out! It was an issue related to formatting of dates. Thank you!

 

[DanteAmor]

You are welcome!

 

[DKRbella0814]

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!