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.

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


"harperma" wrote in message
...
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.

=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.
--
Best Regards,

Luke M


"harperma" wrote:

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.

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.

"Luke M" wrote:

=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.
--
Best Regards,

Luke M


"harperma" wrote:

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.