Excel 2003 forumla help??

[laineybird]

I'm looking for a formula, two column excel 2003:

- first column is the name or title

- second column is the total (number) for each row (not all rows have a total)


I need a formula to SUMIF when using a pivot table for only the sum for 'visible' cells only. Currently just using a SUM, even when the pivot table hides some of the rows but the "grand total" shows the SUM of all cells (even hidden ones).


This is my first pivot table, be gentle please.

 

[Faseeh]

hi laineybird,


Welcome to the Chandoo's forum! Are you summing values with in Pivot Table to extracting it some where else and then summing them? Can you upload a sample file. Just upload it somewhere and paste its link here so that anyone can access it.


Regards,

Faseeh

 

[Luke M]

A PivotTable shouldn't just be "hiding" rows, it should actually be filtering them out. How exactly are you "hiding" the rows? Also, similar to what Faseeh said, I'm a little confused as to why you need a formula is using a PivotTable. Usually, either you have raw data which you use formulas on, or you just use the PivotTable. Could you elaborate on what the situation is?

 

[laineybird]

Sure. I have three projects (project 1, 2, and 3). In the pivot table if I select to see ONLY Project 2 - the "Total" column shows the totals for all three projects even if I filter to Project 2.


I was trying to put a formula in the spreadsheet that eliminates hidden rows. I see what your saying about not manipulating the excel spreadsheet, but I cannot get

"totals" for the filtered items.

 

[laineybird]

I think I've worked it out by changing the layout of the spreadsheet, and it helps to refresh the data on the pivot table before making charts etc. Thanks for your comments.