Sum Ifs Formula needed with Max Date range

**trankim** · 09-19-2012, 08:21 AM

Hi, I am looking for a sum if formula that will look for a max date value from one worksheet and use that as a reference point to sum if a set of data in another worksheet
I have two sets of data. One worksheet has what I call the old historical data (BW-Orders-Old); and the other worksheet has the most "current" data. The sum If formula would ONLY calculate the date ranges from the current worksheet if it's HIGHER than the max date on thte historical worksheet. I am trying to eliminate double counting if someone accidentally pulls data into the Current Worksheet that has data from the BW-Orders-Data tab. I am summing both worksheets together; which is why the max date range should help avoid any duplications. Both worksheets have the exact time column headers and placements. The data is summing into a 3rd worksheet.

Here are the variables:

'BW-Orders-Old'!$P$1:$P$25500 = # of Orders
'BW-Orders-Old'!$A$1:$A$25500 = Date of the actual Order
AN$3 = Date Range in 3rd worksheet used to match against 'BW-Orders-Old'!$A$1:$A$25500
'BW-Orders-D'!$C$1:$C$10418 = Description of Product
$A115 = Description of Product in the 3rd worksheet

=SUMIFS('BW-Orders-Old'!$P$1:$P$25500,'BW-Orders-Old'!$A$1:$A$25500,">="&AN$3,'BW-Orders-Old'!$A$1:$A$25500,"<="&AN$4,'BW-Orders-Old'!$C$1:$C$25500,$A115)+SUMIFS('BW-Orders-D'!$P$1:$P$10418,'BW-Orders-D'!$A$1:$A$10418,">="&AN$3,'BW-Orders-D'!$A$1:$A$10418,"<="&AN$4,'BW-Orders-D'!$C$1:$C$10418,$A115)

Sum If Logic: Look at the max date in the BW-Orders-Old worksheet, and only sum the data in the BW-Orders-D tab if the dates here are HIGHER than the dates in the BW-Old worksheet.

I hope this makes sense and thank you in advance for any help that anyone can provide.

**trankim** · 09-19-2012, 08:23 AM

Hi, I am looking for a sum if formula that will look for a max date value from one worksheet and use that as a reference point to sum if a set of data in another worksheet
I have two sets of data. One worksheet has what I call the old historical data (BW-Orders-Old); and the other worksheet has the most "current" data. The sum If formula would ONLY calculate the date ranges from the current worksheet if it's HIGHER than the max date on thte historical worksheet. I am trying to eliminate double counting if someone accidentally pulls data into the Current Worksheet that has data from the BW-Orders-Data tab. I am summing both worksheets together; which is why the max date range should help avoid any duplications. Both worksheets have the exact time column headers and placements. The data is summing into a 3rd worksheet.

Here are the variables:

'BW-Orders-Old'!$P$1:$P$25500 = # of Orders
'BW-Orders-Old'!$A$1:$A$25500 = Date of the actual Order
AN$3 = Date Range in 3rd worksheet used to match against 'BW-Orders-Old'!$A$1:$A$25500
'BW-Orders-D'!$C$1:$C$10418 = Description of Product
$A115 = Description of Product in the 3rd worksheet

=SUMIFS('BW-Orders-Old'!$P$1:$P$25500,'BW-Orders-Old'!$A$1:$A$25500,">="&AN$3,'BW-Orders-Old'!$A$1:$A$25500,"<="&AN$4,'BW-Orders-Old'!$C$1:$C$25500,$A115)+SUMIFS('BW-Orders-D'!$P$1:$P$10418,'BW-Orders-D'!$A$1:$A$10418,">="&AN$3,'BW-Orders-D'!$A$1:$A$10418,"<="&AN$4,'BW-Orders-D'!$C$1:$C$10418,$A115)

Sum If Logic: Look at the max date in the BW-Orders-Old worksheet, and only sum the data in the BW-Orders-D tab if the dates here are HIGHER than the dates in the BW-Old worksheet.

I hope this makes sense and thank you in advance for any help that anyone can provide.

**trankim** · 09-19-2012, 09:50 AM

I played around with the max and I got the formula to work.

=SUMIFS('BW-Orders-Old'!$P$1:$P$25500,'BW-Orders-Old'!$A$1:$A$25500,">="&BN$3,'BW-Orders-Old'!$A$1:$A$25500,"<="&BN$4,'BW-Orders-Old'!$C$1:$C$25500,$A115,'BW-Orders-Old'!$D$1:$D$25500,$B115)+IF(BN$4>MAX('BW-Orders-Old'!$A$1:$A$25500),SUMIFS('BW-Orders-D'!$P$1:$P$10418,'BW-Orders-D'!$A$1:$A$10418,">="&BN$3,'BW-Orders-D'!$A$1:$A$10418,"<="&BN$4,'BW-Orders-D'!$C$1:$C$10418,$A115,'BW-Orders-D'!$D$1:$D$10418,$B115))