Possibly Sum Product

[-emma-]

Hi All,

I have rows of data, but basically, I want to build up a query (via list in validation) which will enable users to get "Statistics" from the data held.

Column C = Number (any number between 1 - 1000)
Column D = Stage Number (between 1 - 5)
Column E = Open/Closed validation list.

Example :
419 ~ 4 ~ Closed

At the top of the sheet I have built an option...where users can enter a number (between 1 - 1000) in 1 cell, Select a stage via validation in the next cell, and select Open/Closed via validation list.

So...i need (and i think it will be sum product) where when users select 419 ~ 4 ~ Closed, it will search the data and display the number that meet the criteria.

I hope I havnt made this sound much harder that it actually seems...

Any advice will be greatful.

Thanks in advance.

Emma x

[daddylonglegs]

Hello Emma,

Yes, you should be able to use SUMPRODUCT, or if you have Excel 2007 you can use COUNTIFS, e.g. like this

=SUMPRODUCT((C4:C1000=C2)*(D4:D1000=D2)*(E4:E1000=E2))

or COUNTIFS

=COUNTIFS(C4:C1000,C2,D4:D1000,D2,E4:E1000,E2)

where C2:E2 are the criteria cells

[-emma-]

Hi daddylonglegs and thank you for your quick response.

I use 2003 so opted for the sum product one...which worked amazingly...thank you.

For info this was the actual one i used...
=SUMPRODUCT((C7:C1000>=F3)*(D7:D1000=F4)*(E7:E1000=F2))
...please note that > which was critical)

This all seems fine...but I think I may need to make an amendment which i didnt think of before.

What if certain parts of the information when trying to gather statistics is left out...for example...>=101 and Closed...(no mention of the Stage from Column D.

Would that be easy to include?

[daddylonglegs]

In that case, I assume you'd want to use just the column C and E criteria irrespective of the column D value. You can add some IFs into SUMPRODUCT like this

=SUMPRODUCT((C7:C1000>=IF(F3="",C7:C1000,F3))*(D7:D1000=IF(F4="",D7:D1000,F4))*( E7:E1000=IF(F2="",E7:E1000,F2)))

[-emma-]

As always this works great!

Thank you for your quick responses!

Emma x