SUMIFS Formula unparsable

daniel68616

Hello, 

My formula below keeps coming back unparsable and I can't figure out why...The column I need to be summed is referenced first in Range 2. I am also referencing the same date column for that Month and Year criterion. It's on the tip of my tongue but I am running out of options. Any help here would be wonderful. I need range 2 values to be summed when AUTO is selected and the date lands anytime in January 2019.

=SUMIFS({Peterson Family Transactions Range 2}, {Peterson Family Transactions Range 1}, "AUTO", {Peterson Family Transactions Range 3}, MONTH(@CELL) = 1, {Peterson Family Transactions Range 4}, YEAR(@CELL) = 2019)

Thanks for your help in advance:)

Paul Newcome

If you are referencing the same date column for the month and the year, it should be the same range (either 3 or 4). I would start by verifying that. One thing I have found that helps avoid that is by using the AND or OR functions when I am referencing the same range for multiple sets of criteria. I also rename my ranges as "Sheet Name - Column Name" to make for easier referencing when you do run into a problem.

 

Double check your date column ranges first, and if you would like (summary of all this mess is at the bottom)...

 

=SUMIFS({Peterson Family Transactions Range 2}, {Peterson Family Transactions Range 1}, "AUTO", {Peterson Family Transactions Range 3}, AND(MONTH(@CELL) = 1, YEAR(@cell) = 2019))

.

I have also found it useful to use cell references in my formulas as opposed to hard data. If I only need to look at one month at a time, I would use two separate cells elsewhere on the sheet that I can change the month and year in without having to edit the formula itself.

=SUMIFS({Peterson Family Transactions - Sum Range}, {Peterson Family Transactions - Type}, "AUTO", {Peterson Family Transactions - Date}, AND(MONTH(@cell) = [Help Column]$1, YEAR(@cell) = [Help Column]$2))

If I am doing a comparison or year to date tracking, I will build out a table. The specific requirements would determine the actual layout, but there would be a column or row where the months are listed out. I would then use a helper column or row and put the month numbers there and then reference that column or row in my formula. So assuming my months are going down the Month column and my month numbers are in a hidden column titled [Month Number], and I used row 1 of the Month column to input my date, the table would look like this...

Month          Count                 Month Number (hidden)

2019

Jan                  F                               1

Feb                  F                               2

Mar                 F                                3

and my formula would be something along the lines of

=SUMIFS({Peterson Family Transactions - Sum Range}, {Peterson Family Transactions - Type}, "AUTO", {Peterson Family Transactions - Date}, AND(MONTH(@cell) = $[Month Number]@row, YEAR(@cell) = $Month$1))

.

For multiple types...

Type          Jan              Feb            Mar              Apr

2019            1                  2                3                  4

A                   F                 F                F                 F

B                  F                  F                F                 F

and then use in Jan2 (top left F)

=SUMIFS({Peterson Family Transactions - Sum Range}, {Peterson Family Transactions - Type}, $Type@row, {Peterson Family Transactions - Date}, AND(MONTH(@cell) = Jan$1, YEAR(@cell) = $Type$1))

.

Note the use of $ to lock in column and row references. This will allow you to put the formula in the first cell and the drag-fill over and down to populate the rest of the table.

.

If you've read this far (or just skipped to the bottom haha) here's the summary...

Try to set things up so you can directly edit the formula itself as little as possible, and try to condense formulas when possible by using the AND and OR functions when the same range will be used for multiple criteria.

daniel68616

I appreciate the suggestions Paul. This is very helpful and tells me I need to simplify my formulas. I have tried the AND function like you mentioned and am still having no luck...I can't figure it out. I have similar formulas just like this one that work just fine:(. I have also verified that all my references are correct and they are.

Paul Newcome

Are you able to provide some screenshots?

 

The reason I mentioned the references is because you mentioned you are looking at the same range for both the month and year, but your ranges are labeled as 3 and 4. Unless you specifically changed the range names, they should both be the same. Either both 3 OR both 4.

 

I have noticed that sometimes when I am making cross sheet references, if I am moving too fast and the computer or SS a little slow, I will select a row or column, and then the range jumps back to the home cell [Column1]1. I then have to make sure that I re-select the correct range or else the formula will not work properly.

daniel68616

Hi Paul,

So here is the current formula i tried using your suggestions with still no luck. I also thought it was odd that the same date column i was referencing was coming up with a different range number so I thought I would eliminate that as possibility. I did check however before making this change and for some reason still wanted to designate it a different number:/

=SUMIFS({Peterson Family Transactions Range 2}, {Peterson Family Transactions Range 3}, "AUTO", {Peterson Family Transactions Range 1}, AND(MONTH(@CELL) = 1, YEAR(@CELL) = 2019))

Below will be a screen shot of each range I am referencing in the order it is listed in the formula. Hopefully this will help