Excel SUMIF function problem

[harperma]

Hi all,
I'm trying to use something along the lines of the 'sumif' formula but with
a twist.
Basically, i have Column A with a list of repeating names names in no fixed
order. Then in Column B is another list of different but repeating names in
no fixed order. And in Column C is a list of amounts in pounds £.
What i want to do is to look down the complete list of names in column A for
a name 'v' and then look down the list in column B for any name 'w,x,y & z'
and add together all the amounts in column C where V+'w,x,y & z' are present.
In other words if there is a blank in column B the amount in column C will be
ignored.

Sounds really complecated to me and im sure there really is a simple way of
doing it but im too close to the problem to see it, So any help will be
greatly received.
Many Thanks in advance.
MARK.

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A1:A1000="v"),--(B1:B1000<>""),C1:C1000)


note that you need to set a range when using SUMPRODUCT in versions prior to
2007 so you can't use A:A as a range


--


Regards,


Peo Sjoblom

 

[Luke M]

=SUMPRODUCT((A2:A100="V"),(B2:B100="w")+(B2:B100="x")+(B2:B100="y")+(B2:B100="z"),(C1:C100))

Note that with sumproduct, you can not select the whole column, and your
ranges must be equal in length.

 

[harperma]

Hi Luke,
Thanks for such a quick response, unfortunately i think i may not have
explained myself clearly enough.

SHEET 1 (data)
A B C
Mark Steve £1000
Lisa Karen £500
Shell Steve £200
Mark Sue £2500
Shell Steve £750
Mark £100

SHEET 2 (results)
A B
Mark £3500 (search column A for 'MARK' and if there is a name in
column B
then add up all the amounts in column C)
Lisa £ 500 (search column A for 'Lisa' and if there is a name in
column B
then add up all the amounts in column C)
Shell £ 950 (search column A for 'Shell' and if there is a name
in column B
then add up all the amounts in column C)

"As you can see from the above i want to add the amounts for each name in
column A only if there is a name in column B"
So the function in column B on Sheet 2 will be repeated for each name in
column A on sheet 2.

Again, hopes this explains a bit better and thanks in advance for any help
you have.
MARK.