how to multiply a =sumproduct(--(A1:A10,"Jon"), by value in row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Harry Seymour - 13 Jun 2007 09:35 GMT

I've now worked out how to use the following formula:

=SUMPRODUCT(--(A1:A10="Jon"),--(B1:B10=B"),(C1:C10))
which gives me the summed value for jon & B's.

I have now realised that I have a volume column that shows how many of that
particular row there needs to be in the summed value.
e.g.
Name Category Value Volume
Jon A £20 2
Max A £40 1
Jon B £30 3
Mary A £35 2

Would i simply add this extra piece?

=SUMPRODUCT(--(A1:A10="Jon"),--(B1:B10=B"),(C1:C10),(D1:D10))

??

Reply to this Message

Bob Phillips - 13 Jun 2007 09:40 GMT

This is using the documented part of SP <G>

=SUMPRODUCT(--(A1:A10="Jon"),--(B1:B10="B"),C1:C10,D1:D10)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> I've now worked out how to use the following formula:
>
[quoted text clipped - 16 lines]
>
> ??

Reply to this Message

Harry Seymour - 13 Jun 2007 10:05 GMT

Bob,

For some reason this isn't working for me. the equation without D1:D10
works, but adding this new section returns #VALUE

Any thoughts?

Thanks
Harry

> This is using the documented part of SP <G>
>
[quoted text clipped - 20 lines]
> >
> > ??

Reply to this Message

Bob Phillips - 13 Jun 2007 12:59 GMT

Hard to say as it works fine for me.

What are you seeing?

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Bob,
>
[quoted text clipped - 30 lines]
>> >
>> > ??

Reply to this Message

Mike H - 13 Jun 2007 09:57 GMT

almost, try:-

=SUMPRODUCT(--(A1:A10="Jon"),--(B1:B10="A"),(C1:C10*D1:D10))

Mike

> I've now worked out how to use the following formula:
>
[quoted text clipped - 15 lines]
>
> ??

Reply to this Message