Multiple Search Criteria

[jwwhite021]

Hey,

Hope you can help me with the following situation.

I need to add up the total of a column if it agrees with two criteria, that :

a - it only adds up the value of goods marked 2009
b - it only adds those columns that state success

Background: Got three columns of data

a - Year End Date
B Back log of projects in value
c - Progress to date

I just want to add up those that are a success for each year so that I can calculate the potential revenue with different levels of project success.

HOpe you can help.

thank you

John

 

[Stormseed]

Can you show us an example of what you want to accomplish in real values alongwith your expected results ?

 

[jwwhite021]

ya sure

So I am creating a table with two columns

Year Total Backlog for that year (but only for projects labeled Success)

2009 25,000
2010 47,000

I presently have three columns, (Example of data line below)

Backlog Year End Status
25,000 2009 Success
36,000 2009 Fail
47,000 2010 Success

So the formula must look up columns A (Backlog), and columns c (STatus) and add up the value of the projects deemed a success in 2009

Hope that helps?

thanks

John

 

[sanrv1f]

assuming your data is in A1:A4 and summary table is in E1:F3

F2

=SUMPRODUCT(--($B$2:$B$4=E2),--($C$2:$C$4="Success"),$A$2:$A$4)

and copied down

 

[Stormseed]

Formula entered in cell E4, assuming the year is entered in cell E2 and the Status is entered in cell F2:

Book15.xls
	A	B	C	D	E	F
1	Backlog	Year	Status		Year	Status
2	25000	2009	Success		2009	Success
3	36000	2009	Fail
4	47000	2010	Success		25000
Sheet1

Formula:

=SUMPRODUCT(--($B$2:$B$4=$E$2),--($C$2:$C$4=$F$2),($A$2:$A$4))