DUMB Question: SUMIFS with only one date... but multiple entries in about 10000 rows...

[WERNER SLABBERT]

[TABLE="width: 2563"]
<tbody>[TR]
[TD]Ref[/TD]
[TD]Company[/TD]
[TD]Date[/TD]
[TD]Company[/TD]
[TD]INV Number[/TD]
[TD]Sub Total[/TD]
[TD]Expenses[/TD]
[TD]Difference[/TD]
[TD]Grand Total[/TD]
[TD]Paid[/TD]
[TD]Credit Status[/TD]
[TD]Date Sum[/TD]
[TD]Outstanding Amount[/TD]
[TD]Sum by Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Test[/TD]
[TD]2018/10/03[/TD]
[TD]Test 2018/10/03_Test0001[/TD]
[TD]Test0001[/TD]
[TD]R3 888,00[/TD]
[TD]R2 110,00[/TD]
[TD]-R 4 002,00[/TD]
[TD]R 5 998,00[/TD]
[TD]Yes[/TD]
[TD]OPEN[/TD]
[TD][/TD]
[TD]-R 4 002,00[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]43377[/TD]
[TD]43376[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]test[/TD]
[TD]2018/10/03[/TD]
[TD]test 2018/10/03_yrgcbgvbcvb[/TD]
[TD]yrgcbgvbcvb[/TD]
[TD]R64 320 750,00[/TD]
[TD]R32 456,00[/TD]
[TD]R 59 763 506,00[/TD]
[TD]R 64 353 206,00[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]R 59 763 506,00[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD]43376[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]asdfghjkl[/TD]
[TD]2018/10/04[/TD]
[TD]asdfghjkl 2018/10/04_asdfgh45678[/TD]
[TD]asdfgh45678[/TD]
[TD]R129 925 025,00[/TD]
[TD]R234 567 890,00[/TD]
[TD]R 361 036 126,00[/TD]
[TD]R 364 492 915,00[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]R 361 036 126,00[/TD]
[TD="align: right"]0,00[/TD]
[TD][/TD]
[TD]43377[/TD]
[/TR]
</tbody>[/TABLE]

How do i get the "Sum by Date" (Column "O" or SBD ) to sum all the rows in "Grand Total" (Column "J" or GrndTot ) that has the same date in "Date" (Column "D" or DT ) and only show result in the last row that has the specific date , this has to apply to 10 000 rows and dates an rows might change.

I have tried my hand at the SUMIFS [ =SUMIF(DT;$P$8;GrndTot) ]

any help would be appreciated alot. i've been stuck here for about 2 weeks now.

PS: The last two columns are just the date selector ( date in excel years) and the date from DT (in excel years)

 

[Yongle]

Try something like this

=IF(COUNTIF($D$2:D2,D2)=COUNTIF(D:D,D2),SUMIF(D:D,D2,J:J),"")

Count the number of cells with the same date up to this row (date is in column D)
If that is the same as the total count of cells with the same date put the total in this row (otherwise leave it blank)
The total is the sum of all values carrying that same date (values to sum are in column J)

 

[WERNER SLABBERT]

Hi Yongle
Thanx for the reply, i have tried it, but to no avail. still just returns '0,00'

i just changed your formula to my range names
=IF(COUNTIF(DT;$F$5)=COUNTIF(DT;$F$5);SUMIF(DT;$F$5;GrndTot);"")

Regards

 

[Yongle]

For you to look at...

1. Why did you use the same range for both COUNTIF formula?
- think about the logic of your test
- it can never return FALSE becase the value returned by both COUNTIF is identical

your formula
=IF(COUNTIF(DT;$F$5)=COUNTIF(DT;$F$5)....
my formula
=IF(COUNTIF($D$2:D2,D2)=COUNTIF(D:D,D2)....

How do i get the "Sum by Date" (Column "O" or SBD ) to sum all the rows in "Grand Total" (Column "J" or GrndTot ) that has the same date in "Date" (Column "D" or DT )

2. Is there something missing or incorrect in what you requested?
- my formula refers to columns D & J
- your formula also includes a reference to column F

3. Did you test my formula unmodified (except regional settings ";" instead of "," ) ?
- it works for me based on the information you provided
- even if your data starts in a dfferent row I think it will work

I will be offline for 48 hours minimum

 

[Ashutosh Kumar]

Do you desire to get something like this?

<tbody>
[TD="class: xl63"]Ref[/TD]
[TD="class: xl63"]Date[/TD]
[TD="class: xl63, width: 81"]Values[/TD]
[TD="class: xl63, width: 62"]Sum by Date[/TD]

[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl66, width: 73, align: right"]03-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]23[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl66, width: 73, align: right"]03-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]16[/TD]
[TD="class: xl63, width: 62, align: right"]39[/TD]

[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl65, width: 73, align: right"]04-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]21[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]4[/TD]
[TD="class: xl65, width: 73, align: right"]04-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]20[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]5[/TD]
[TD="class: xl65, width: 73, align: right"]04-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]16[/TD]
[TD="class: xl63, width: 62, align: right"]57[/TD]

[TD="class: xl63, width: 64, align: right"]6[/TD]
[TD="class: xl64, align: right"]05-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]42[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]7[/TD]
[TD="class: xl64, align: right"]05-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]20[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]8[/TD]
[TD="class: xl64, align: right"]05-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]0[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]9[/TD]
[TD="class: xl64, align: right"]05-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]40[/TD]
[TD="class: xl63, width: 62, align: right"]102[/TD]

[TD="class: xl63, width: 64, align: right"]10[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]7[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]11[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]26[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]12[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]33[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]13[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]49[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]14[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]12[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]15[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]46[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]16[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]30[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]17[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]29[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]18[/TD]
[TD="class: xl64, align: right"]06-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]24[/TD]
[TD="class: xl63, width: 62, align: right"]256[/TD]

[TD="class: xl63, width: 64, align: right"]19[/TD]
[TD="class: xl64, align: right"]07-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]9[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]20[/TD]
[TD="class: xl64, align: right"]07-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]48[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]21[/TD]
[TD="class: xl64, align: right"]07-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]24[/TD]
[TD="class: xl63, width: 62, align: right"][/TD]

[TD="class: xl63, width: 64, align: right"]22[/TD]
[TD="class: xl64, align: right"]07-10-2018[/TD]
[TD="class: xl63, width: 81, align: right"]0[/TD]
[TD="class: xl63, width: 62, align: right"]81[/TD]

</tbody>

You may use the formulae =SUMIF(Date,C7,Grand_Total)
and if your dates are in ascending or descending order, you may use conditional formatting to view only the last row of each date.

 

[WERNER SLABBERT]

F5 in mine is just a cell that contains the selected date to process. by means of a drop down calendar. ( mainly to be able to select a specific date to sum.)

how can i send you a sample book?

 

[WERNER SLABBERT]

F5 in mine is just a cell that contains the selected date to process. by means of a drop down calendar. ( mainly to be able to select a specific date to sum.)

how can i send you a sample book?

I don't seem to be getting my thoughts across , my brain and fingers are not working together today, here is a link to the workbook complete.

http://www.filedropper.com/gvscounterbetaversion11