sumif be by categories

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have description in Col A. I need a sumif formula or another formula to sum the data where I have summarised the descriptions into categories


See sample data below


It would be appreciated if someone could assist me



Book1
AB
1COS Shirts Used1595
2COS Tracking Used2584
3O/all PRF Used956
4O/all PHJ Used1478
5
6
7
8Category Summary
9COS
10O/all
11
Sheet1
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Is this what you need, copied down?

Excel Workbook
AB
1COS Shirts Used1595
2COS Tracking Used2584
3O/all PRF Used956
4O/all PHJ Used1478
5
6
7
8Category Summary
9COS4179
10O/all2434
SUMIF
 
Upvote 0
thanks Peter. This is perfect. I was not sure how to incorporate the wild card character into the criteria. with your assistance, I now now
 
Upvote 0
Hi Peter

I have another query pertaining to Sumif to add anything that contains Sales and Int in Col b for eg Sales Internal Labour, Sales Int etc, where sales and Int or Sales Internal appears in the text in Col B


your assistance in resolving this is most appreciated




See sample data below. I have several other categories, but once I have resolved this one, then I should be able to do the others as well




Book1
AB
1Sale Lab Internal 14,846
2Sale Lab Internal3,276
3Sale S/let Internal2,007
4Sale Oils/Other Int478
5Sale Consumables Int37
6
7
8
9Sale Int0
10
Sheet1
Cell Formulas
RangeFormula
B9=SUMIF(A1:A5,A9&"*",B1:B8)
 
Upvote 0
.. anything that contains Sales and Int ..
Assuming that the Sale & Int come in the order shown in cell A9 ..

Excel Workbook
AB
1Sale Lab Internal14,846
2Sale Lab Internal3,276
3Sale S/let Internal2,007
4Sale Oils/Other Int478
5Sale Consumables Int37
6
7
8
9Sale Int20644
SUMIF




Nice one Pete. Just Beautiful!!! :)
Hi John, hope things are well with you. Good to hear from you. :)
 
Upvote 0
Thanks Peter-magic formula. It works perfectly
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top