converting SUM(IFS) for Partial Text Match to identifying the data that's being sumed up

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
137
Office Version
  1. 365
Platform
  1. Windows
Hi All, how do i turn: =SUMIFS($C$4:$C$18,$A$4:$A$18,"*"&F6&"*") - into a function that helps me to identify/highlight/mark the values that are being summed up. F6 = equals two letters - AT. Basically sumifs all the values that have the letter 'AT' in them. The formula works fine but I don't know which cells are being added. If I can identify the values using a helper column (or something) then I can filter them and see what's included in the total. I would also like to know how to just identify the (partial) values in cells without using SUMIFS. Many thanks in advance.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Could use conditional formatting to do something like this.

Book1
AB
1Search Term:cat
2
3Catastrophe
4Category
5KitKat
6Concatenate
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A6Expression=ISNUMBER(SEARCH("*" & $B$1 & "*",A3))textNO
 
Upvote 0
Irobbo314, thank you for the prompt reply. conditional formatting doesn't always highlight the values that get summed up. When I filter, the sum of highlighted cells doesn't add up to the sumifs. I have to manually reconcile it to find the difference. Is there a function that I can use in a helper column that, say, puts a '*' or something in the cells if its included in sumifs total? I don't have the option - Cells with Conditional Formatting - in my rules.
 
Upvote 0
Irobbo314,.....having said that I have never used a formula to do conditional formatting before, so your formula could work 100% for me - not having to reconcile manually. I've always put in key words. Once again, thank you!
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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