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

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)

--
www.stfx.ca/people/bliengme


"Jose Mourinho" wrote:


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

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!

"Bernard Liengme" wrote:

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)

--
www.stfx.ca/people/bliengme


"Jose Mourinho" wrote:


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

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

--
John Mansfield
http://cellmatrix.net





"Jose Mourinho" wrote:

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!

"Bernard Liengme" wrote:

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)

--
www.stfx.ca/people/bliengme


"Jose Mourinho" wrote:


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