Dynamic Range Help

[dluhut]

To all Excel experts,

I have a file here that I've been trying to do the total counts based on 2 criterias, namely number and month. To be more clearer, I have attached a file.

The goal is simple, yet, I can't achieve it. On Sheet1 where all datas are gathered, I would like to create a cumulative frequencies distribution, but it has to meet the 2 criterias, which is on "Dynamic Frequencies Sheet".

Thus, taking number 15 for example, it should have given me an answer of 3 on cell B16 & C16 (number 15 appears 3 times on the month of January and February from Sheet1) on Dynamic Frequencies Sheet, 2 on cell D16 and E16 (number 15 appears twice on the month of March and April).

I believe the attached file explains it clearly. Also, taking this opportunity, thank you in advanced for helping me out.

[Marcol]

Here's one way

In B2

Please Login or Register  to view this content.

Drag/Fill Down to Row 50 Then across to Column M

Note
Any month that is not in your sheet "Sheet1" will return #N/A, you could if you want suppress this error with this IF statement in B2

Please Login or Register  to view this content.

Hope this helps.

There is probably a better way using SUMPRODUCT, or dynamic named ranges, I just can't get my head around them at the moment!

[dluhut]

That's perfect Marcol! Exactly what I wanted.

I've tried Sumproducts too, but it's not working, that's why I decided to post them up here.

Are you going to try with different methods? If not, then I'm going to make this as solved.

Also, please check if your reputation has increased, as I'll be adding mine to yours after I've post this reply.

Once again, thank you!

[Marcol]

Here's a way to use dynamic named ranges.

1/. Create a dynamic named range for each Month
e.g.
Name:= Jan
Refers to:=

Please Login or Register  to view this content.

2/. In Sheet "Dynamic Frequencies" B2

Please Login or Register  to view this content.

Drag/Fill Down to Row 50.

3/. Unfortunately INDIRECT() doesn't like refering to dynamic named ranges so we can't drag this across by using INDIRECT(B$1) in place of Jan in step 2, so each month column has to be separately entered.
To do this Drag B2 across to C2 then in C2 change Jan to Feb (2 places) then double click the Fill Handle (the small square bottom right of the highlighted cell C2), this will fill down to row 50 as defined by Column B.

Repeat this for all the months.

Hope this helps.

Thanks for the Rep+

Don't know if I will have time to crack the SUMPRODUCT solution tonight, my brain's fried, and the Pub beacons.... .... .... might just give me in"spirit"ation!!!