SUM data if meets criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jose Mourinho - 29 May 2007 12:02 GMT

I want to sum data in cells when the data meets the criteria given in
seperate cells. SUMIF cannot handle this; can someone advise another approach
using formula?

e.g. project type is defined in Col A, Yearly data in Col B, C, E
How to sum yearly data of Col B for each project type?

Many thanks

JM

Reply to this Message

Bernard Liengme - 29 May 2007 12:50 GMT

Why can you not use SUMIF?
=SUMIF(A1:A100,"First Project",B1:B100)

You do not tell what columns C and E are about.

One way would be to use a pivot table

Another is SUMPRODUCT
=SUMPRODUCT(--(A1:A100="First Project"), B1:B100)

Signature

www.stfx.ca/people/bliengme

> I want to sum data in cells when the data meets the criteria given in
> seperate cells. SUMIF cannot handle this; can someone advise another approach
[quoted text clipped - 6 lines]
>
> JM

Reply to this Message

Jose Mourinho - 29 May 2007 14:33 GMT

Apologies, I should have been clearer> I have data arranged like this in
Columns:

Project Type Values Yr1 Yr2 Yr3:
1 A Product1Yr1 Prod1Yr2 Prod1Yr3
1 A Product2Yr1 Prod2Yr2 P2Yr3
2 B Product1Yr1 P1Yr2 Yr3 ...
2 B Product2Yr1 P2Yr2 Yr3 ...
3 A Product1Yr1 P1Yr2 Yr3 ...
3 A Product2Yr1 P2Yr2 Yr3 ...

I want sum of Product1 for Project Type A in Yr1

I can easily do it with filters (can try a Pivot table too I suppose- never
used them), but wondered whether a formula could handle this?

Many thanks!

> Why can you not use SUMIF?
> =SUMIF(A1:A100,"First Project",B1:B100)
[quoted text clipped - 16 lines]
> >
> > JM

Reply to this Message

John Mansfield - 29 May 2007 20:06 GMT

You should be able to do so using SUMPRODUCT. There are many examples
(including the link below) of how to use SUMPRODUCT on the web.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Signature

John Mansfield
http://cellmatrix.net

> Apologies, I should have been clearer> I have data arranged like this in
> Columns:
[quoted text clipped - 34 lines]
> > >
> > > JM

Reply to this Message