SUM data if meets criteria

[Jose Mourinho]

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

 

[Bernard Liengme]

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)

 

[Jose Mourinho]

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!

 

[John Mansfield]

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