Help with the Sum function

[Dan Wilson]

Good day. I have spent the last hour trying to make this work, but I no luck. I have a worksheet that contains a list of "Services", "Installations" and "Repairs" to my motorcycle. For instance, "3/1/2024" is in Columns A. "Replaced Battery" is in Column B, "135.00" is in Column C and "R" is in Column D. I am trying to total the costs that match "S", "I", or "R" in Column D. There are currently 32 items in the list, but I don't think that has anything to do with the problem. I have tried SUMIF and SUMIFS, with no luck. For some reason, my brain is not working. I appreciate any help with this one.
Thanks, Dan...

 

[bebo021999]

suppose column D contains text like "S...", or "I...", or "R...", column C contains value need to be summed
with maximum rows is 100
There are several ways, try:
=SUMIF($D$1:$D$100,"S*",$C$1:$C$100) + SUMIF($D$1:$D$100,"I*",$C$1:$C$100) + SUMIF($D$1:$D$100,"R*",$C$1:$C$100)
or
=SUMPRODUCT(( (LEFT($D$1:$D$100)="S") + (LEFT($D$1:$D$100)="I") + (LEFT($D$1:$D$100)="R") )*$C$1:$C$100)

 

[Dan Wilson]

Good day bebo021999 and THANK YOU for responding. I used the SUMIF formula and found that the "$" is absolutely necessary to make it work. This forum is a life-saver and very educational. I will experiment with the SUMPRODUCT function as I have never used it.
Thank you, Dan...

 

[Peter_SSs]

Do you have the GROUPBY function? If so, is this what you want?

25 03 31.xlsm
	B	C	D	E	F	G
1	Description	Amount	Type		Type	Amount
2	Replaced Battery	135.00	Repair		Installation	423.00
3	Schedule Service	200.00	Service		Repair	381.00
4	Fixed tail light	45.00	Repair		Service	355.00
5	Installed something	423.00	Installation		Total	1,159.00
6	Replaced Windshield	201.00	Repair
7	Extra Service	155.00	Service
8
Motorcycle

Cell Formulas
Range	Formula
F2:G5	F2	=GROUPBY(D2:D7,C2:C7,SUM)
Dynamic array formulas.

Otherwise, what about using Excel's built-in Pivot Table feature (on the 'Insert; ribbon tab)?

25 03 31.xlsm
	B	C	D	E	F	G
1	Description	Amount	Type		Row Labels	Sum of Amount
2	Replaced Battery	135.00	Repair		Repair	381.00
3	Schedule Service	200.00	Service		Service	355.00
4	Fixed tail light	45.00	Repair		Installation	423.00
5	Installed something	423.00	Installation		Grand Total	1,159.00
6	Replaced Windshield	201.00	Repair
7	Extra Service	155.00	Service
8
Motorcycle (2)

.. or is it something else you want?

 

[Teeroy]

Good day bebo021999 and THANK YOU for responding. I used the SUMIF formula and found that the "$" is absolutely necessary to make it work. This forum is a life-saver and very educational. I will experiment with the SUMPRODUCT function as I have never used it.
Thank you, Dan...

@Dan Wilson, since you've indicated that you wanted to look into sumproduct there is a small error in the sumproduct formula that, while it will work correctly (in this case), may confuse you.
Sumproduct should have multiple ranges separated by commas, so this should be:

=SUMPRODUCT(( (LEFT($D$1:$D$100)="S") + (LEFT($D$1:$D$100)="I") + (LEFT($D$1:$D$100)="R") ),$C$1:$C$100)

or use the sum formula instead:

=SUM(( (LEFT($D$1:$D$100)="S") + (LEFT($D$1:$D$100)="I") + (LEFT($D$1:$D$100)="R") )*$C$1:$C$100)

Personally I'd use a pivot table to summarise this data as PeterSSs has suggested as it gives the information about the sub-groups and the whole together if (when, from my experience ) you need it in future.

 

[Dan Wilson]

Do you have the GROUPBY function? If so, is this what you want?

25 03 31.xlsm
	B	C	D	E	F	G
1	Description	Amount	Type		Type	Amount
2	Replaced Battery	135.00	R		I	423.00
3	Schedule Service	200.00	S		R	381.00
4	Fixed tail light	45.00	R		S	355.00
5	Installed something	423.00	I		Total	1,159.00
6	Replaced Windshield	201.00	R
7	Extra Service	155.00	S
8
Motorcycle

Cell Formulas
Range	Formula
F2:G5	F2	=GROUPBY(D2:D7,C2:C7,SUM)
Dynamic array formulas.

Otherwise, what about using Excel's built-in Pivot Table feature (on the 'Insert; ribbon tab)?

25 03 31.xlsm
	B	C	D	E	F	G
1	Description	Amount	Type		Row Labels	Sum of Amount
2	Replaced Battery	135.00	R		I	423.00
3	Schedule Service	200.00	S		R	381.00
4	Fixed tail light	45.00	R		S	355.00
5	Installed something	423.00	I		Grand Total	1,159.00
6	Replaced Windshield	201.00	R
7	Extra Service	155.00	S
8
Motorcycle (2)

.. or is it something else you want?

Good day Peter_SSs and thank you for responding. The issue has been resolved, but I ill investigate your response. I don't know if I have the GROUPBY function, but it looks interesting.
Thank, Dan...

 

[Dan Wilson]

@Dan Wilson, since you've indicated that you wanted to look into sumproduct there is a small error in the sumproduct formula that, while it will work correctly (in this case), may confuse you.
Sumproduct should have multiple ranges separated by commas, so this should be:

=SUMPRODUCT(( (LEFT($D$1:$D$100)="S") + (LEFT($D$1:$D$100)="I") + (LEFT($D$1:$D$100)="R") ),$C$1:$C$100)

or use the sum formula instead:

=SUM(( (LEFT($D$1:$D$100)="S") + (LEFT($D$1:$D$100)="I") + (LEFT($D$1:$D$100)="R") )*$C$1:$C$100)

Personally I'd use a pivot table to summarise this data as PeterSSs has suggested as it gives the information about the sub-groups and the whole together if (when, from my experience ) you need it in future.

Good day Teeroy and thanks for responding. The issue has been resolved and thank you for the update on the SUMPRODUCT function. I appreciate the update.
Thanks, Dan...