Excel Formula Help

[DrH100]

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.

 

[steve the fish]

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]

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.