Sum If Then formula help

[Jon-W]

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

[#2]

@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)

[#3]

@Sergei Baklan 

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

[#4]

@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

[#5]

@Sergei Baklan 

They are sorted first by date, then by card type.

[#6]

@Jon-W 

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

[#7]

@Sergei Baklan 

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

[#8]

@Jon-W 

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. 

[#9]

@Sergei Baklan 

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!!

[#10]

@Sergei Baklan 

Any thoughts? You have been such a help so far!!

[#11]

@Jon-W 

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.

[#12]

@Sergei Baklan 

You are THE BEST!! Thank you so much for your help!!

[#13]

@Jon-W 

You are welcome, glad to help