SUMIFS with Date ranges getting #VALUE!

With this forums help, came up with the following, to sum vendor totals throughtout a multi column/row range, where the totals came from 9-10 columns:

=SUMIF(AW$21:BE$573,C3, AX$21:BF$573)

Now I am tasked to break out totals by month. I was coming up with a #value! error so I broke it down. Started with, and no problems with:

=SUMIFS(AX$21:BF$573,AW$21:BE$573,C3), this totaled up fine.

The error occurred when I added date range criteria, with this formula:

=SUMIFS(AX$21:BF$573,AW$21:BE$573,C3,K$21:K$573,">=02/01/2016",K$21:K573,”<=02/29/2016”) ( this is the formula I am trying to get to work)

I double checked the dates in the K column were formatted the same as my formula, and it didn’t make any difference. I tried with only one date. So I tried the SUMIFS, with the sum range of only one column, and it worked in this:

=SUMIFS(AX$21:AX$573,K$21:K$573, ">=02/01/2016",K$21:K$573,"<=02/29/2016")

The only way I have figured it out is to duplicate the SUMIFS for each of my 5 separate columns in one cell, but this is burdensome (eg. = sumifs column B + sumifs column D + etc)

I should note the monthly total I am calc-ing on separate tab from my data, don’t know if this has any adverse effect.

I received some help on this forum a few weeks ago, and am very appreciative for the expertise and time you guys spend solving peoples headaches. And note I wouldn’t post on here without first spending considerable time and effort to sorting out on my own….so thank you in advance. Sorry to be long winded, but figure more info is better than less.

The ranges in the SUMIFS function have to be the same size, i.e. same number of rows and same number of columns. Your dates are only one column wide, so this is why you are getting the error.

You might be able to do it using SUMPRODUCT instead of SUMIFS, but it would help if you attached a sample Excel workbook, so we can play about with different formulae to check that they work with your ACTUAL data layout (you mention getting data from a different tab, but your formulae don't show that).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Thanks, Pete. My work book had proprietary info, so I had to re-create something. This should give an idea.

A couple of notes
I have several dates columns, but monthly totals should be taken from this one only (submitted).
There are blanks in the date column. I cannot remove these items, as they are proposals that have not yet been submitted, be submitted in the future and have a
unique tracking number already assigned. Line items with a blank date should not be totaled in the monthly tracker until the proposal is submitted.
Blank rows at the bottom are for future entries and I will insert more rows as needed.

I think the blank date cells are causing the error.
TIA,
HS

Incidentally, note the attached is for Subcontractors, I will need to duplicate this for Vendors

Originally Posted by HeadSkrach

... I think the blank date cells are causing the error ...

No, it's because your ranges are not the same size, as I said before.

I've tried several things, but the best I can come up with is 3 separate SUMIFS in the same cell. Note that I have changed the entries on row 1 of the Monthly sheet so they now contain dates that are formatted to show just the month, so the formula in C2 becomes:

=SUMIFS('Dates & $s'!$D:$D,'Dates & $s'!$C:$C,$B2,'Dates & $s'!$B:$B,">="&C$1,'Dates & $s'!$B:$B,"<="&EOMONTH(C$1,0)) +
SUMIFS('Dates & $s'!$F:$F,'Dates & $s'!$C:$C,$B2,'Dates & $s'!$B:$B,">="&C$1,'Dates & $s'!$B:$B,"<="&EOMONTH(C$1,0)) +
SUMIFS('Dates & $s'!$H:$H,'Dates & $s'!$C:$C,$B2,'Dates & $s'!$B:$B,">="&C$1,'Dates & $s'!$B:$B,"<="&EOMONTH(C$1,0))

Note that the only change is in the first range of the SUMIFS term, shown in red (I've manually put line-breaks in the formula on the forum, to make it even clearer). You can use full-column references with SUMIFS with no reduction in performance, so this makes the formula easier to follow, and also helps if you add more data in the future. This formula can be copied across to N2, and then down to row 11.

On the other sheet I've given you a sub-total row at the bottom of your data, so this responds if you apply filters to your data.

Hope this helps.

Pete

Thanks, Pete. I did use a version of what you suggested. I have no experience with the &EOMONTH(C$1,0) portion and had trouble getting that to work. What i did come up with:

=SUMIFS('Change Orders 2016'!$AX$21:$AX$585,'Change Orders 2016'!$AW$21:$AW$585,$C2,'Change Orders 2016'!$K$21:$K$585,">01/31/2016",'Change Orders 2016'!$K$21:$K$585,"<03/01/2016")+SUMIFS('Change Orders 2016'!$AZ$21:$AZ$585,'Change Orders 2016'!$AY$21:$AY$585,$C2,'Change Orders 2016'!$K$21:$K$585,">01/31/2016",'Change Orders 2016'!$K$21:$K$585,"<03/01/2016")+SUMIFS('Change Orders 2016'!$BB$21:$BB$585,'Change Orders 2016'!$BA$21:$BA$585,$C2,'Change Orders 2016'!$K$21:$K$585,">01/31/2016",'Change Orders 2016'!$K$21:$K$585,"<03/01/2016")+SUMIFS('Change Orders 2016'!$BD$21:$BD$585,'Change Orders 2016'!$BC$21:$BC$585,$C2,'Change Orders 2016'!$K$21:$K$585,">01/31/2016",'Change Orders 2016'!$K$21:$K$585,"<03/01/2016")+SUMIFS('Change Orders 2016'!$BF$21:$BF$585,'Change Orders 2016'!$BE$21:$BE$585,$C2,'Change Orders 2016'!$K$21:$K$585,">01/31/2016",'Change Orders 2016'!$K$21:$K$585,"<03/01/2016")

work fine, but it was a little tedious changing the dates in the top row. When I have a slow down, I am going to try and incorporate the &EOMONTH (x, x) portion.

I was hoping to simplify it a bit, as I learned on this forum you can do a SUMIF for ranges that are shifted, as this works:

=SUMIF('Change Orders 2016'!AW$21:BE$585,E7,'Change Orders 2016'!AX$21:BF$585).

Regards,
HS

Thanks, Jonmo1, I got this to work very easily. Not sure if i understand how it works, like the use of * It would seem this is not used for multiplying but more of 'in conjunction with' . Either way, appreciate it.

You're welcome.

The * actually is used for multiplying.

($AW$21:$BE$573=C3)
This creates an array of TRUE or FALSE results, is the value in the cell = C3
The same for the other 2 arrays
($K$21:$K$573>=DATE(2016,2,1)) - is the date greater than 2/1/2016
($K$21:$K$573<=DATE(2016,2,29)) - is the date less or equal to 2/29/2016

When a math operation like * is applied to a True or False value, they are considered 1 and 0
TRUE = 1, FALSE = 0

So you end up with an array of a bunch of 1's and 0's.

Now IF normally expects an expression of either TRUE or FALSE
But similarly, IF will consider 1 as TRUE, and 0 as FALSE.

So taking it 1 row at a time
=SUM(IF(($AW$21=C3)*($K$21>=DATE(2016,2,1))*($K$21<=DATE(2016,2,29)),$AX$21))
Each row results in either a 1 or 0 for each of the 3 criteria, and they are multiplied together.

Naturally by math rules, only if all 3 values are 1 will the result of the math be 1.
Any 1 of the 3 numbers are 0, then the overall result of the math is 0.

It then sums the corresponding values in $AX$21:$BF$573, but only if the relevant 3 conditions were true/1


Hope that helps.

Yes, it certainly does with your explanation. Arrays are something i am not familiar and am reading up on them now.
Thanks again and Regards/