# Excel Formula Help

#### DrH100

##### Board Regular
Afternoon All

I've cobbled together the following formula to give a Subtotal with multiple options when a column is filtered but it seems to be counting some things twice.

I'm sure there is a far more elegant formula I could use but the one I am using is below.

=((SUMPRODUCT(SUBTOTAL(3,OFFSET(J\$41:J\$1000,ROW(J\$41:J\$1000)-MIN(ROW(J\$41:J\$1000)),,1)),ISNUMBER(SEARCH("AL",J\$41:J\$1000))+0))+(SUMPRODUCT(SUBTOTAL(3,OFFSET(J\$41:J\$1000,ROW(J\$41:J\$1000)-MIN(ROW(J\$41:J\$1000)),,1)),ISNUMBER(SEARCH("AL pm",J\$41:J\$1000))+0))+(SUMPRODUCT(SUBTOTAL(3,OFFSET(J\$41:J\$1000,ROW(J\$41:J\$1000)-MIN(ROW(J\$41:J\$1000)),,1)),ISNUMBER(SEARCH("AL am",J\$41:J\$1000))+0)))

I thought it was working fine as it seems to be counting all the items with AL only fine but if any cells with AL am or AL pm in it is counting them twice. I'm assuming its because of the AL in all three options as if I change one of them to something unique it doesn't do it. Unfortunately that's not possible for this sheet.

Any thoughts and help would be most appreciated as I don't really understand what the formula is doing to know where to correct it.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### steve the fish

##### Well-known Member
ISNUMBER(SEARCH is essentially seeing if AL is contained in the cell. That will be true for AL, AL pm and AL am so therefore you will be double counting. Could you give us examples of what is in those cells?

#### DrH100

##### Board Regular
ISNUMBER(SEARCH is essentially seeing if AL is contained in the cell. That will be true for AL, AL pm and AL am so therefore you will be double counting. Could you give us examples of what is in those cells?
Thanks Steve - Essentially the cells are a drop down with a number of options but its only the AL, AL am and AL pm ones that do not have unique contents so all the others work perfectly fine. If that is what the ISNUMBER(Search is looking for then I should be able to find something that is unique and replace it with for those three items

Thanks for your help though as I've now read what the SEARCH function looks at and learnt something new - everyday is a school day.

Replies
2
Views
97
Replies
10
Views
225
Replies
5
Views
128
Replies
4
Views
141
Replies
3
Views
124

1,171,630
Messages
5,876,545
Members
433,199
Latest member
guerin47

### 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.

### Which adblocker are you using?

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

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