Excel formula not adding correctly due to a '.'

We are using this excel formula to display the owner pertinence (=SUMPRODUCT(SUBTOTAL(3,OFFSET($H$14:$H$26,ROW($H$14:$H$26)-MIN(ROW($H$14:$H$26)),,1)),ISNUMBER(SEARCH(G9,$H$14:$H$26))+0)).  But when the columns are not filtered, it counst wrong lines that ends with a ".", like "Lexus." and "Trans.".  When we filter the  data, all workd well, its' when it is not filtered.  We have included the excel.  Please advice what we are doing wrong.
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
ooops! forgot the attachmente
FilterSum.xlsx
0
AlanConsultantCommented:
Hi,

You can use COUNTIF for this.

See attached solution - yellow cells appear to be correct.

Alan.
EE-29076706-FilterSum-Version1.xlsx
0
rayluvsAuthor Commented:
Prior using "=SUMPRODUCT(SUBTOTAL(3,..." I had COUNTIF.  The problem is when using the filter the result is based on the entire row, not on filter.  With =SUMPRODUCT(SUBTOTAL(3,, the results is displayed as per filtered.

The problem I am facing is when no filter: if the data's  first part of the "Pert" is same with other rows, it counts them all.  Notice when unfiltered the "Lexus." at top, its says 2, when there is only 1 (it is becuase its also counting 'Lexus. BL') - when unfiltered your COUNTIF is correct.

However, if I filter selecting SMITH and Daysi, the result should display only 3 in "Pert" column with values = BMW (2), Lexus. (1) and Lexus. BL (1).  In your COUNTIF, the values don't change.  For example, when filtered it says that FORD (3) when there is no Ford in the filter result (see pix below).

example formula COUNTIF when filter
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AlanConsultantCommented:
Hi,

I see what you mean.

Try this version instead (attached).

Does that solve the problem?

Thanks,

Alan.
EE-29076706-FilterSum-Version2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Thank  you Yes!

Can you brief a bit in the fromula where it complied to the need?

=SUMPRODUCT(SUBTOTAL(3,OFFSET($H$14:$H$26,ROW($H$14:$H$26)-MIN(ROW($H$14:$H$26)),,1))*($H$14:$H$26=G4))
0
AlanConsultantCommented:
Hi,

The first part is identical to what you had. See

The second part:

 ($H$14:$H$26=G4)

Just multiplies, row by row, the values in the lower table.

Hope that helps.

Alan.
0
rayluvsAuthor Commented:
Thanx!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.