CODEWITHSUNDEEP
excelexcel-formula
the vulgar
the vulgar

Reputation: 11

excel SUMIFS only on same date

I'm trying to create a formula in column K which sums all cells that apply , in column J, only when the following conditions are true:

For example, in cell K2, I want the sum of J2+J3+J4 because A2=A3=A4 and B2=B3=B4.

K5=J5 only, because there are no other dates with the same client name.

K6=J6+J7 because A6=A7 and B6=B7.

What kind of formula would I use for this? I can't figure out how to do it with a SUMIFS.

Photo of sheet

Upvotes: 0

Views: 4012

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

In cell K2 put following formula:

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,SUMIFS($J$2:$J$10,$A$2:$A$10,A2,$B$2:$B$10,B2),"")

  • Adjust row 10 value. It will be last row of your actual data.
  • Copy down as much you need.

EDIT

Uploaded file shows the cause behind formula not working correctly for you. It turned out to be whitespace characters in column B (names) data e.g.

  • Cell B3: "Moe John" has a trailing space.

  • Cell B10: Same case with "Doe Jane"

If you want to use above posted formula then all names shall be corrected. Or alternatively to deal with spaces you can adopt below approach.

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,"*"&TRIM(B2)&"*")=1,SUMIFS($J$2:$J$28,$A$2:$A$28,A2,$B$2:$B$28,B2),"")

Notice the change in COUNTIFS formula where B2 is now replaced with "*"&TRIM(B2)&"*".

Even such formula will take a beating if you have uneven whitespace characters in between your data. I'd suggest normalizing it as much as possible.

Upvotes: 0

Chef1075
Chef1075

Reputation: 2724

I would try using a pivot table with:

  • The names as row values
  • The dates as the column values
  • And funds received using SUM in the values column

Edit

Based on @pnuts comments here is how to get the values in column K. Put this in K2 and drag down.

=IF(OR(COUNTIFS($B$1:B3, B3) = 1, B3 = ""), SUMIFS($J$2:J2, $A$2:A2, A2, $B$2:B2, B2), "")

This formula will give blank values until the formula finds a new client on a new date. However, I still think using pivot table is a better solution.

However, I still find the pivot table

Upvotes: 1

Related Questions