Excel Formula Help

DrH100

Board Regular
Joined
Dec 30, 2011
Messages
72
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
Joined
Oct 20, 2009
Messages
8,722
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2011
Messages
72
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.
 

Forum statistics

Threads
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.
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
Top