Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 788
Default SUMIF - Pivot table and months

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!!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default SUMIF - Pivot table and months

You could use a GetPivotData formula instead, as described he

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

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

Chris wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumif for months and year Kevin Excel Worksheet Functions 2 November 6th 07 07:03 PM
correct order of months in pivot table Rocky Excel Discussion (Misc queries) 6 June 19th 07 05:44 PM
Pivot table displaying last 12 months Acro Charts and Charting in Excel 1 June 15th 06 10:28 PM
Pivot table blank cells show data deleted months ago! vcard Excel Discussion (Misc queries) 0 April 25th 06 09:25 PM
SUMIF for Dates / Months ? ZMAN Excel Worksheet Functions 5 August 26th 05 02:07 AM


All times are GMT +1. The time now is 09:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"