SUM using different Text

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Hello,

Please assist if possible. an example may be best
A B
1 ABC 100
2 ABC Pty Ltd 100
3 ABC P/L 100

ABC is the same company written differently in each cell
(due to many users), I would like to know the total
of "ABC" being $300. Using the following uncomplete array
formula
=SUM((A1:A3="what do I put here")*B1:B3)
Can u only enter the first few letters of the word & have
a variable 4 the rest? I would prefer not to find &
replace as my data base is accross numerous worksheets &
each name may be written differently in all worksheets.

ThankYou for your time
Mx
 
One possible way

=SUMPRODUCT(--ISNUMBER(FIND("ABC",A1:A3)),--(B1:B3))

you could also use

=SUMIF(A1:A3,"*ABC*",B1:B3)
 
Hi,

Thankyou for your assistance, could u please advise if I
was to take this one step further, see example

A B C
1 ABC 100 Yes
2 ABC Pty Ltd 100 No
3 ABC P/L 100 Yes
Click to expand...

How could I show only the result of column B if yes in
column C i.e $200 using formula =SUMIF(A1:A3,"*ABC*",B1:B3)
thankyou
Mx
 
One way

=SUMPRODUCT(--ISNUMBER(FIND("ABC",A1:A3)),--(C1:C3="Yes"),--(B1:B3))

so you can't really use sumif for more than one condition and more than 2
ranges,
the above will work though
 
Back
Top