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?
 
AlanConnect With a Mentor ConsultantCommented:
Hi,

I see what you mean.

Try this version instead (attached).

Does that solve the problem?

Thanks,

Alan.
EE-29076706-FilterSum-Version2.xlsx
0
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
 
AlanConnect With a Mentor ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.