SumIF for more than one condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kippers - 26 Oct 2007 11:38 GMT

Hi,

I am wanting to sum up data in a column only when it corresponds to data in
2 other columns, i.e.

Stage Person Cost
Stage A Person 1 £5
Stage A Person 2 £2
Stage A Person 4 £8
Stage A Person 1 £7
Stage A Person 1 £10
Stage B Person 4 £12
Stage B Person 4 £1
Stage C Person 5 £3

What is the easiest way of calculating how much it costs for Person 1 in
Stage A? (actual spreadsheet contains hundreds of rows)

cheers,

Reply to this Message

VIVEKMANE - 26 Oct 2007 11:45 GMT

Hi Kippers,
Use Filter Option to know the amount, first filter by Person1
and then filter by Stage A. You will get a result.

Regards,
Vivek Mane

> Hi,
>
[quoted text clipped - 15 lines]
>
> cheers,

Reply to this Message

Bob Phillips - 26 Oct 2007 11:58 GMT

=SUMPRODUCT(--(A2:A10="Stage A"),--(B2:B10="Person 1"),C2:C10)

Signature

HTH

Bob

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

> Hi,
>
[quoted text clipped - 16 lines]
>
> cheers,

Reply to this Message

Sandy Mann - 26 Oct 2007 12:13 GMT

One way:

=SUMPRODUCT((A2:A9="Stage A")*(B2:B9="Person 1")*C2:C9)

or with Stage A in D1 and Person 1 in E1:

=SUMPRODUCT((A2:A9=D1)*(B2:B9=E1)*C2:C9)

Note that you appear to have a double space in Person 1

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hi,
>
[quoted text clipped - 16 lines]
>
> cheers,

Reply to this Message