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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,545
Members
416,923
Latest member
jarri

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