SUMIF problem

[toolagio]

i want to do a sumif, but I need to do something to a LIKE o
CONTAINS condition

For instance, my values can b
a1
a2
b1
a3
a4

I want to do a sumif, something like this sumif(a1:a5,"LIK
'a'",b1:b5)

Is this possible

thank

 

[Stephen]

Hi,

What I would do is make an extra column next to it with the formul
=MID(B2,1,1). That will get the first letter out of the column (th
"a" or "b"). And then have your SUMIF formula refer to that ne
column.
It is a easy solution, but it works.

Stephen

 

[Norman Harker]

Hi toolagio!

Use:

=SUMIF(A1:A7,"a",B1:B7)

Note that the range in A1:A7 is the same number of cells as in B1:B7
You must put a in quotation marks or Excel will look for the named
variable a

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
try
=SUMIF(A1:A5,"a*",B1:B5)

 

[Norman Harker]

Hi toolagio!

Doh! Misread your question first time:

Here's an array formula approach:

=SUM(IF(LEFT(A1:A7)="a",1)*B1:B7)
Entered by pressing and holding down Ctrl + Shift and then pressing
Enter.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.