Share via

How to sum all costs if a particular activity code is present on that date

Anonymous
2019-04-08T00:57:26+00:00

I've been going nuts trying to figure this out.  I have a spreadsheet with ~165,000 lines and what I would like is a formula to sum the "Total Cost" for a particular "Account" when a "Activity Code" is a certain value (i.e. 10) only for those "Activity Code" on the same same "Service Date".

For example, below since for account 1234, "10" was present on 11/21/2017, the total cost result would be $2131.14.  

If "10" had not been present on that service date for that account, the formula would not sum the total cost.

Account Activity Code Service Date Total Cost
1234 1 11/21/2017 $43.02
1234 2 11/21/2017 $177.07
1234 3 11/21/2017 $30.76
1234 4 11/21/2017 $4.32
1234 5 11/21/2017 $4.32
1234 6 11/21/2017 $2.44
1234 7 11/21/2017 $15.38
1234 8 11/21/2017 $8.28
1234 9 11/21/2017 $11.44
1234 10 11/21/2017 $1,751.85
1234 11 11/21/2017 $42.01
1234 12 11/21/2017 $40.25
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-04-08T12:27:58+00:00

    Another option:

    Enter below Formula in cell I2 and copy down:

    =IF(COUNTIFS(A$2:A$50,F2,B$2:B$50,H2,C$2:C$50,G2),SUMIFS(D$2:D$50,A$2:A$50,F2,C$2:C$50,G2),"N/A")

    This checks columns A / B / C for any occurrence of ALL F2  H2 / G2 values respectively, and if yes then returns the SUM from column D of all activity codes for F2 & G2.

    Regards,

    Amit Tandon

    www.excelanytime.com

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-04-08T02:00:14+00:00

    A "rough" way to get this, which will reproduce the total on every line matching the Activity code and service date, would be to use the following formula (assuming your data in columns A:D

    =IF(SUMIFS($B$2:$B$15,$A$2:$A$15,$A2,$C$2:$C$15,$C2,$B$2:$B$15,"=10")=10,SUMIFS($D$2:$D$15,$A$2:$A$15,$A2,$C$2:$C$15,$C2),"No result")

    See column F in the picture.

    If you wanted to make it a bit more aesthetic by having just one entry per Activity Code/Service date combination you would need to create a unique list of those pairs and then change the formula to (for example)

    =IF(SUMIFS($B$2:$B$15,$A$2:$A$15,$H2,$C$2:$C$15,$I2,$B$2:$B$15,"=10")=10,SUMIFS($D$2:$D$15,$A$2:$A$15,$H2,$C$2:$C$15,$I2),"No result")

    See columns H:J in the picture.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-04-08T01:54:26+00:00

    Hi

    Please try any of this formulas

    =IF(ISNUMBER(MATCH(G3,$B$2:$B$13,0)),SUMIFS($D$2:$D$13,$A$2:$A$13,G2,$C$2:$C$13,G4),"")

    or

    =IF(ISNUMBER(MATCH(10,$B$2:$B$13,0)),SUMIFS($D$2:$D$13,$A$2:$A$13,G2,$C$2:$C$13,G4),"")

    Please check picture below

    Hope this will help you

    Do let me know if you require any further help on this. Will be glad to help you.

    Thanks

    Regards

    Jeovany CV

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-04-08T01:42:23+00:00

    There is only one item in your list for Activity code 10 so the total should be: 1751.85.  this formula will give you that:

    =SUMIF(B2:B13,"=10",D2:D13)

    Put your table in range: A1:D13 but also make sure the Total Cost column is values and NOT text.

    Was this answer helpful?

    0 comments
  5. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more