SUMIF - Pivot table and months


C

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!!
 
Ad

Advertisements

D

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!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top