# sumifs, show sum only once in the last instance



## abusuzuki (Dec 30, 2022)

Hello, I have this table as shown in the picture, where I need to calculate the total amount according to each employee for each date. I used the sumifs function for that, but I want the sum which is in colum E to appear only at the last instance for each employee ,  I need to sum the total of column E for all rows where B and C both match the values of current row, but I need to get the sum only ONCE at the last instance , not on every row where they match, And if possible, I would like to apply the formula for the coming days, so I do not want the formula to be confined to column 15 ,How do I do this?


----------



## jdellasala (Dec 30, 2022)

PLEASE use *XL2BB* when posting data.
Book1ABCD1DateNameAmtRunning Total212/29/2022Jhon10001000312/29/2022Jhon10002000412/29/2022David12001200512/29/2022David10002200612/30/2022David20004200712/30/2022Jhon10003000812/30/2022David20006200912/30/2022David20008200Sheet1Cell FormulasRangeFormulaD2:D9D2=SUMIFS($C$2:C2,$B$2:B2,B2)


----------



## Fluff (Dec 30, 2022)

Hi & welcome to MrExcel.
How about
Fluff.xlsmABCDE1DateNameAmtRunning Total229/12/2022Jhon1000 329/12/2022Jhon10002000429/12/2022David1200 529/12/2022David10002200630/12/2022David2000 730/12/2022Jhon10001000830/12/2022David2000 930/12/2022David200060001031/12/2022 1131/12/2022 1231/12/2022 1331/12/2022 14 MainCell FormulasRangeFormulaE2:E14E2=IF(AND(C2<>"",COUNTIFS(B$2:B2,B2,C$2:C2,C2)=COUNTIFS(B:B,B2,C:C,C2)),SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2),"")


----------



## shift-del (Dec 30, 2022)

Hi
That is a task for a pivot table.
Mappe8BCDEFGH1DateNameAmtDateNameSumme von Amt229.12.2022Jhon100029.12.2022David2200329.12.2022Jhon1000Jhon2000429.12.2022David120030.12.2022David6000529.12.2022David1000Jhon1000630.12.2022David2000730.12.2022Jhon1000830.12.2022David2000930.12.2022David2000Tabelle1


----------



## abusuzuki (Dec 30, 2022)

The formula that given by jdellasala seems useful and enriches the field, but currently the formula given by Fluff is exactly what I need right now. Thank you very much to both of you for the quick response. I will keep both formulas.


----------



## Fluff (Dec 30, 2022)

Glad we could help & thanks for the feedback


----------

