Jun 10 2019 12:36 PM - edited Jun 10 2019 12:42 PM
I need help with a formula, I think it's going to be a If Then sum formula, but I haven't had a lot of luck trying to figure this out on my own. In my example, I want to sum the payment amount (E), if the date field (B) and the form of payment field (D) are the same. Any help would be greatly appreciated!!
A | B | C | D | E | F | G |
Decatur | 5/2/2019 | 2019 CASH DECATUR | Master Card | 211905020004 | $376.05 | |
Decatur | 5/2/2019 | 2019 CASH DECATUR | Visa | 211905020002 | $253.21 | |
Decatur | 5/4/2019 | 2019 CASH DECATUR | Visa | 211905040002 | $170.04 | |
Decatur | 5/6/2019 | 2019 CASH DECATUR | Master Card | 211905060002 | $530.71 | |
Decatur | 5/6/2019 | 2019 CASH DECATUR | Visa | 211905060001 | $504.67 | |
Decatur | 5/7/2019 | 2019 CASH DECATUR | Discover | 211905070006 | $1,098.72 | |
Decatur | 5/7/2019 | 2019 CASH DECATUR | Discover | 211905070007 | ($1,098.72) | |
Decatur | 5/7/2019 | 2019 CASH DECATUR | Master Card | 211905070012 | $327.10 | |
Decatur | 5/8/2019 | 2019 CASH DECATUR | Visa | 211905080003 | $1,030.90 | |
Decatur | 5/8/2019 | 2019 CASH DECATUR | Visa | 211905080004 | $257.23 | |
Decatur | 5/15/2019 | C&C ASPHALT INC. | American Express | 211905150005 | $284.45 | |
Decatur | 5/15/2019 | HOOD, LARRY | Visa | 211905150006 | $561.68 | |
Decatur | 5/16/2019 | 2019 CASH DECATUR | Master Card | 211905160002 | $1,340.91 | |
Decatur | 5/17/2019 | 2019 CASH DECATUR | Master Card | 211905170003 | $1,526.20 | |
Decatur | 5/17/2019 | 2019 CASH DECATUR | Visa | 211905170001 | $1,276.19 | |
Decatur | 5/20/2019 | 2019 CASH DECATUR | Master Card | 211905200001 | $378.01 |
Jun 10 2019 01:09 PM
@Jon-W ,
It depends on how you'd like organize the result. If like
then
=SUMIFS($G:$G,$D:$D,J$1,$B:$B,$I2)
Jun 10 2019 01:34 PM - edited Jun 10 2019 01:35 PM
This is how I need to format it, this is how it looks when I just add it myself.
Kennesaw | 5/2/2019 | PROCONTRACTOR SUPPLY, INC. | Master Card | $855.00 | 551905020002 | $855.00 |
Kennesaw | 5/3/2019 | 2019 CASH KENNESAW | Master Card | $254.40 | 551905030006 | $254.40 |
Kennesaw | 5/3/2019 | MAX JONES | Visa | $1,470.40 | 551905030004 | $1,470.40 |
Kennesaw | 5/4/2019 | APEX SEAL COATINGS | Master Card | $482.72 | 551905040001 | $482.72 |
Kennesaw | 5/6/2019 | APEX SEAL COATINGS | Master Card | $321.82 | 551905060001 | $321.82 |
Kennesaw | 5/14/2019 | 2019 CASH KENNESAW | Visa | $281.59 | 551905140007 | $281.59 |
Kennesaw | 5/15/2019 | 2019 CASH KENNESAW | Visa | 551905150006 | $58.29 | |
Kennesaw | 5/15/2019 | 2019 CASH KENNESAW | Visa | $411.27 | 551905150012 | $352.98 |
Kennesaw | 5/16/2019 | 2019 CASH KENNESAW | Visa | $136.21 | 551905160005 | $136.21 |
Kennesaw | 5/23/2019 | 2019 CASH KENNESAW | Visa | $154.74 | 551905230003 | $154.74 |
Kennesaw | 5/24/2019 | 2019 CASH KENNESAW | Visa | 551905240006 | $529.47 | |
Kennesaw | 5/24/2019 | 2019 CASH KENNESAW | Visa | $618.50 | 551905240007 | $89.03 |
Kennesaw | 5/28/2019 | 2019 CASH KENNESAW | Master Card | $437.23 | 551905280006 | $437.23 |
Kennesaw | 5/29/2019 | 2019 CASH KENNESAW | Master Card | $335.23 | 551905290002 | $335.23 |
Kennesaw | 5/29/2019 | 2019 CASH KENNESAW | Visa | $719.38 | 551905290005 | $719.38 |
Jun 10 2019 01:49 PM
@Jon-W ,
And do records sorted by dates in ascending order and when by card type? Or for the same date records could be like
Visa
MasterCard
Visa
MasterCard
Jun 10 2019 01:51 PM
Jun 10 2019 01:59 PM
When it could be like in column I in Sheet2 attached
=IF(($B1=$B2)*($D1=$D2),"",SUMIFS($G:$G,$B:$B,$B1,$D:$D,$D1))
Start from row 1 and drag down
Jun 10 2019 02:39 PM
That is EXACTLY what I need, but I'm not an Excel expert, and can't figure out how you got the formula into that whole row. When I use formulas, so far they have been basic. =A1*A2 and then I would copy and paste that formula into all the cells below it. This what I'm coming up with when I try your formula. I will paste here, and then link a copy of the excel file. And thank you so much for your help!!
Date | CustomerName | Payment | SaleNum | Amount | ||
5/1/2019 | 2019 CASH BIRMINGHAM | Cash | 462 | 471905010002 | $462.00 | =IF(($B2=$B3)*($D2=$D3),"",SUMIFS(#REF!,$B2:$B21,$B2,$D2:$D21,$D2)) |
5/2/2019 | B & T LABORING | Cash | $190.30 | 471905020005 | $190.30 | |
5/8/2019 | 2019 CASH BIRMINGHAM | Cash | 93.5 | 471905080006 | $93.50 | |
5/9/2019 | 2019 CASH BIRMINGHAM | Cash | 27.5 | 471905090007 | $27.50 | |
5/13/2019 | 2019 CASH BIRMINGHAM | Cash | 471905130002 | $492.80 | ||
5/13/2019 | 2018 CASH BIRMINGHAM | Cash | 156.75 | 471905130006 | $156.75 | |
5/15/2019 | WARREN ASPHALT CONSTRUCTION | Cash | 471905150003 | $8.79 | ||
5/15/2019 | 2018 CASH BIRMINGHAM | Cash | 471905150004 | $331.91 | ||
5/15/2019 | 2018 CASH BIRMINGHAM | Cash | 471905150006 | $165.54 | ||
5/15/2019 | 2018 CASH BIRMINGHAM | Cash | 471905150007 | $2.75 | ||
5/15/2019 | 2019 CASH BIRMINGHAM | Cash | 64.89 | 471905150008 | $64.89 | |
5/18/2019 | BIG JOHN'S ASPHALT PAVING | Cash | 13.2 | 471905180002 | $13.20 | |
5/21/2019 | 2019 CASH BIRMINGHAM | Cash | 492.8 | 471905210002 | $492.80 | |
5/23/2019 | 2019 CASH BIRMINGHAM | Cash | 471905230001 | $372.89 | ||
5/23/2019 | 2019 CASH BIRMINGHAM | Cash | 471905230008 | $367.40 | ||
5/23/2019 | 2019 CASH BIRMINGHAM | Cash | 943.8 | 471905230009 | $943.80 | |
5/24/2019 | 2019 CASH BIRMINGHAM | Cash | 471905240002 | $243.91 | ||
5/24/2019 | 2019 CASH BIRMINGHAM | Cash | 722.7 | 471905240008 | $722.70 | |
5/25/2019 | SOUTHERN SEALS AND STRIPES | Cash | 724.9 | 471905250002 | $724.90 | |
5/31/2019 | 2019 CASH BIRMINGHAM | Cash | 176 | 471905310005 | $176.00 |
Jun 10 2019 03:07 PM
In your case formula will be like
=IF(($A2=$A3)*($C2=$C3),"",SUMIFS($F$2:$F2,$A$2:$A2,$A2,$C$2:$C2,$C2))
Be careful which columns to take for the calculation and with absolute / relative references.
Condition parameter in IF checks if the date AND payment method are the same in the current and next row. If so then empty string. If no, then SUMIFS calculates sum of all payments from first to current row for which the date and payment method are are the same as for current row.
Jun 10 2019 04:30 PM
You are a life saver!! I have one more snag, and probably something I should have mentioned earlier. In this sheet, there are multiple locations, which might have transactions on the same day for the same form of payment. I have 4 locations, the formula you gave works perfect for the first store, but on the second store, it starts to add the totals from the first store.
This is your formula
=IF(($B1=$B2)*($D1=$D2),"",SUMIFS($G:$G,$B:$B,$B1,$D:$D,$D1))
This is your formula, tweaked for my sheet, that does work on the different categories for my first store
=IF(($B2=$B3)*($D2=$D3),"",SUMIFS($G$2:$G2,$B$2:$B2,$B2,$D$2:$D2,$D2))
This is what I came up with when I tried to add a 3rd condition
=IF(($B2=$B3)*($D2=$D3)*($A2=$A3),"",SUMIFS($G$2:$G2,$B$2:$B2,$B2,$D$2:$D2,$D2))
That still worked for the first location, but then added the daily total from the store above.
Once again, I appreciate the help so much!!
Jun 11 2019 12:53 PM
Jun 11 2019 02:37 PM
Sorry, I missed your question. That could be
=IF($A2="","",IF(($B2=$B3)*($D2=$D3)*($A2=$A3),"",SUMIFS($G$2:$G2,$B$2:$B2,$B2,$D$2:$D2,$D2,$A$2:$A2,$A2,$A$2:$A2,"<>")))
in cell E2 and drag it down till end of the range over all locations.
Jun 12 2019 04:52 AM