SumIF/CountIF data

  • Thread starter Thread starter SumIF
  • Start date Start date
S

SumIF

I have a range of data that I would like to apply the
sumif and countif functions. However, some cells are not
fully included, most likely because they are too long.
There can be as many as 30 company names within a cell,
and I have functions that is counting or summing
instances of a whole list of companies. However,
companies that appear in the 20th place, for instance, do
not appear because the string is likely to long.

Is there a way to get around this problem?

Potential solutions that I thought about (but do not know
how to fully implement), would be to create a
new "excess" column that includes all companies that are
after the last char(10) prior to 255 characters, which is
likely the ending point for the countif search.

Thanks
 
How about

=SUMPRODUCT(--(NOT(ISERROR(FIND("Ford",A1:A100)))))

to count them

Word of warning, it will add TransFord as well.
 
Supposing that componies are comma-separated, try for counting:

=SUMPRODUCT(--ISNUMBER(SEARCH(Company,","&$A$1:$A$10&",")))

I thrust you can extend the above for conditional summing.
 
Hi Bob

a little bit shortened :-)
=SUMPRODUCT(--(ISNUMBER(FIND("Ford",A1:A100))))

and to deal with 'transFord' one may use
=SUMPRODUCT(--(ISNUMBER(FIND(" Ford "," " & A1:A100
& " "))))
 
Thanks. This solves the problem of counting the
instances of data (countif solution), but I am also
trying to get a formula to return something similar to
the sumif.

For instance, I have two columns of data. One is the
textual data that you provided the countif solution
(sumproduct(--.....)), but I would also like to sum up
the corresponding data in the second column whenever I
hit these instances of excess data within a cell. Is
there a solution for that too?

Thanks again
 
Hi
then use:
=SUMPRODUCT(--(ISNUMBER(FIND(" Ford "," " & A1:A100
& " "))),B1:B100)

to sum column B
 
Sorry, I figured the problem out.

I should have tried a little harder first before replying.
 
Back
Top