Using SUMIF to calculate Current Month - on Pivot Table (OLAP)

[hierosir]

Hi guys,

Thank you all in advance for even taking the time to read this. I appreciate it.

Here is a table that comes from an OLAP analysis server:

I need to have on a "dashboard" else where a field that will show the "Current Month's" acquisitions (you see them in Row 22, in Grand Total row). Additionally, I'll need a "Previous Month" figure.

This is what I have currently for "Current Month":

=SUMIF('Data Acq'!C1:NC1,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),'Data Acq'!C25:NC25)-SUMIF('Data Acq'!C1:NC1,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),'Data Acq'!C25:NC25)

It returns a "0" value, I just can't figure out why.

Thanks for your help,

Cheers,

Hierosir

 

[Jerry Sullivan]

Hi Hierosir and Welcome to MrExcel,

A couple things to check....
1. The formula you posted references a sum_range C25:NC25, but your screen shot and description show Row 22 instead of Row 25.

2. The second part of your formula should be a "less than" symbol instead of "greater than":
"... -SUMIF('Data Acq'!C1:NC1,"<"&DATE(...."


If you have Excel 2007, consider using the SUMIFS function.

Will the location of your Grand Total row always be the same? With PivotTables, one needs to consider filtering or changes in the data that would affect the number of rows.

 

[hierosir]

Hi Jerry!

Thank you so much for responding. After reading this I went over and realized two things. a) on point 1) you made you were correct. This was a temporary error in troubleshooting it myself (had removed subtotals thus changing the pivot). Then b) I realized that I had incorrectly converted the date's running across the pivot. They're in text, as opposed to date. Converted and then finally it worked. Alll good times.

On 2. indeed you are correct - thank you for that!

Hope you're well and the week continues to kick butts for you!

-Hierosir