count & sum ranges from Pivot

**luke_p** · 03-20-2009, 07:41 AM

Hi All,

I have a pivot table (sheet1)that has dates along the top(jan to dec 08 and will eventually include 2009) and codes down the left side.

The pivot will change on a daily basis, i.e. more codes will be added, as the new date comes up.

On sheet2 is my basis formula's that count or sum up on a monthly basis but with a fixed range, so if the pivot chnages the formala does not!!

Can someone help me with a formala that is dynamic enough to cope with this, i.e. maybe using the month to help with the calculation?

Please see my example spreadsheet to see what i mean.

Thanks

**arthurbr** · 03-20-2009, 08:10 AM

Hi,
maybe it would be better to post an XLS file instead of xlsx
Not everyone has 2007 yet

**NBVC** · 03-20-2009, 08:12 AM

Try these:

in B9:

Please Login or Register  to view this content.

in C9:

Please Login or Register  to view this content.

in D9:

Please Login or Register  to view this content.

in E9:

Please Login or Register  to view this content.

each copied down

**luke_p** · 03-20-2009, 08:54 AM

Thanks NBVC,

your formala works for the 30,60 & 75 but not for ">75" as this can be any multiple of the above 3 e.g. 90, 105, 120 etc.. etc...

Also, you still have to specify a range for dates.

is ther eno way of tellijg the formula to see if the month is in the column and pick up the data for that month ?

(also spreadsheet as 2003)

**NBVC** · 03-20-2009, 09:27 AM

Ok.

See attached... Now it is both vertically and horizontally dynamic.

first.. better to change your dates in row 7 of Sheet1 to actual dates and not text strings and then format to desired appearance... I did that in the attached for you...

Now use Sumproduct formulas in columns B and D of sheet2 and simply multiplication formulas in C and E.

I used column A to reference number to search for but you can hard code into formula.. you just won't be able to copy down and get a self adjusting formula.. you will have to hard code the number into each.

To take care of the >75, I simply said >A11 which contains the same 75...

Hope this helps.

**luke_p** · 03-23-2009, 04:48 AM

NBVC, many many thanks.

one last question with regards the formula, more so i understand whats going on;

in the formula you have

Please Login or Register  to view this content.

what does this match to (what does it mean) ?

Sorry thinking about it, if you have the time, can you give me a step by step as to what the formula is doing ?

**NBVC** · 03-23-2009, 07:48 AM

Match(9.999999e+307,A:A) looks for the position of the last numeric entry in column A...

9.9999999e+307 (maybe a couple more 9's) is the largest number that Excel recognizes and the Match() function without the optional 3rd argument looks through the range and finds the last entry that is smaller than or equal to the number being looked up (i.e. 9.99999999e+307)