Need data sorted on dynamic selection

I have data that I need to be dynamically sorted in the highlited manner. Any help is greatly appreciated. The values on the selection criteria are dynamic

To sum the encounters per month and ED positions, put this in K2:

=SUMPRODUCT(($E$2:$E$104=K$1)*($C$2:$C$104=$J2)*($F$2:$F$104))

And copy over and down as needed.

Thank you so much for giving me the forumula. Actually I need logic for the following
if column h = column a and column I = column b then get number of encounters by criteria(vertically) and month(horizontal) for that year and name (selected dynamically).
Is this even possible ?
i have attached in the excel ....
The criteria and months display needs to be dynamic

I cant thank you enough to help me again !!!1

Thanks for the reply .But my name and year selection will be dynamic also the tool i am working with cannot use filters. Also I will need the critieria to display dynamicaly.

In this spreadsheet put this in I3:

=SUMPRODUCT(($C$3:$C$104=$I3)*($E$3:$E$104=J$2)*($F$3:$F$104))

And copy over and down as needed.

The formula I've given you does that. It's looking up something in the row above and something from the column to the left and adding up all the matches for F.

The references to those lookups are locked for row and column respectively, so that when the formula is moved around it will always still reference whatever you enter in I or row 2.


If you change H3 to CR3, the data will automatically adjust.

Thanks for the reply. This is what I want. I will try my best to be more clear. All I have is data in columns A to F . Columns G and H will get populated dynamically. Columns g will have the name ( A or B) and column H will have the year. The want the output kind of in a pivot table. If the dynamic selection is A and 2012 in column G and H then get me all the data for that selection by pivoted by criteria
criteria jan feb march april may jun july aug sep oct nov dec
Cr1 110110 0 0 0 0 0 0 0 0 0 0
CR2 0 0 0

Thanks you so much again for helping me ...

Thanks but I cannot use pivot table. The tool I am using wouldnt allow me to do it.

I am using crystal excelcius. I get data from a report and lay it into excel and from excel ( all the tool allows is to build formulas... no arrays as well) we pull it in a dashboard. There are limiatations when I use excel as middle end.

Okay ....Can I match the name ,year and criteria dynamically to get the months diaplayed in the year horizontally inthe excel ...

if I have input as A , 2011 and CR1 then display months horizontally....

This should do it. I added an IF to column G to determine which records meet your dynamic selection, and then appended my SUMPRODUCT to only match those records.

You can change K6, L6, I14, I15, I16, J12, K12, L12, and M12 to fit whatever you want.

Attachment 260286

Thank you so much daffodil .What I am trying to say is even my months have to be displayed dynamically through a formula. If i select year 2011 then only the months in that year have to be displayed. I am unable to get the months diplayed horizontally through a formula ... .

Like this?

Attachment 260296

Yes .. thanks a lot this is exactly what I was looking for. I need another help. Just found out that sumproduct is buggy in xcelsius. Is there a work around for sumproduct. I cannot use sumifs as well also array functions are not supported.

Thanks again for all your help....

You have excluded the only three Excel functions I know that can accomplish data management:

• Array formulas with braces
• Functions that utilize arrays such as SUMIFS, SUMPRODUCT
• Pivot Tables (which are utilize the same logic as above behind the scenes)

(your data is in an array)

The formula works perfectly find in excel. When i connect to my components (charts) nothing gets displayed. I opened a ticket yesterdayregarding it already as they mentioned that sumproduct works with the tool and its not working.... so there is a bug in the tool. Thanks for all your help...i figured out a alternate solution to implement a pivot table. !!!!

Thanks a lot Daffodil for giving me ideas and formulas...

No problem, glad I could help. Thanks for the feedback.