SUMIF - Pivot table and months

[Chris]

Hi all

I am working on a dashboard for management reporting and got stuck on my
cumulative totals.

I have exported the data from my accountancy software and thrown it in a
pivot in the following format

Date (initial data in dd/mm/yy but then grouped
by month)

Jan Feb Mar ...

Acc number [Sum of transaction]

Now, on my dashboard I need an interactive cumulative total so I wrote the
following:

Cell A1 "<- this is a filter drop down menu - format can be"
(Select March/Mar/3 (format is flexible) = cumulative to the end of March)

Cell A2
=SUMIF("month range in pivot table","<="&A1,"Acc number transaction range")

The problem is the criteria field doesn't appear to work, any ideas??

Thanks!!

 

[Debra Dalgleish]

You could use a GetPivotData formula instead, as described here:

http://www.contextures.com/xlPivot06.html

Use a cell reference in the formula, that links to a cell with the month
number.

Hi all

I am working on a dashboard for management reporting and got stuck on my
cumulative totals.

I have exported the data from my accountancy software and thrown it in a
pivot in the following format

Date (initial data in dd/mm/yy but then grouped
by month)

Jan Feb Mar ...

Acc number [Sum of transaction]

Now, on my dashboard I need an interactive cumulative total so I wrote the
following:

Cell A1 "<- this is a filter drop down menu - format can be"
(Select March/Mar/3 (format is flexible) = cumulative to the end of March)

Cell A2
=SUMIF("month range in pivot table","<="&A1,"Acc number transaction range")

The problem is the criteria field doesn't appear to work, any ideas??

Thanks!!