Not Counting Grand Total from Pivot Table

[baca25]

Ok I'm doing something for work and I am running into a problem.

I am trying to count pivot tables, and my method may be a lot harder of getting my desired results, but its the only way I could figure out how to do what I needed.

I have run into a problem where I need to not include the Grand Total of a pivot table into my count. entering "<>"&"grand total cell" does not work to exclude it from the count.

I have split the same pivot table into two pivot tables because I needed to get the counts of the different categories.

I can not edit or draw formulas from the original data at all, but my table under the "Result" tab should be able to update if more data is entered or taken out.

Cells F5 and G5 are what need to be looked at in the Result tab.

Everything else is just an example of what I'm doing and how I'm doing it.

Thanks for the help!Counting Pivot Table.xlsx

[Abdul Basit]

Dear Bro.

Try This Formula

=COUNTIFS('Pivot Category 2'!C:C,"<>"&"",'Pivot Category 2'!C:C,">=20")-1

[baca25]

sorry but its not that simple. If the data were updated for green or purple to be above $20, then it would count 1 more then it should because the grand total would be above $20. and it would have to be -2 and not -1.

[FDibbins]

Hi and welcome to the forum

see if the attached will give you what you need. It uses a combination of count/offset/indirect

[baca25]

It does seem to be working.
Thanks for the help, now I'm going to try to dissect the formula and make sense out of it.

[FDibbins]

Sorry, in reviewing some of those formulas, I noticed a mistake...attached is the corrected version

[baca25]

Ok thanks, its also important to know that the 20 you entered for the height should be a larger number for my work where there is a lot more data.

I can't seem to wrap my head around most of the formula. But it works and thats mostly what matters.

For instance I cant seem to understand ("'pivot Category "&$A5&"'!A4"). where is pivot category , what does "&$A5&" mean.

You dont have to answer that, just some of what I'm struggling to understand in the formula.

[FDibbins]

That is actually part of the INDIRECT() function, which I used so that you could copy the formula down (for other sheets) without needing to edit/fiddle with it to change sheet names...
INDIRECT("'pivot Category "&$A4&"'!A4")

Indirect() is a function that converts text to a reference that excel can use in a formula.
"'pivot Category "&$A4&"'!A4" by itself will give 'pivot Category "1'!A4, which may look like a sheet/cell reference, but all excel sees is some text - indirect() converts that

your sheet names are "Pivot Category 1", "Pivot Category 2" etc, and in your table, you have 1 (A4) and 2 (A5), so I used "'Pivot Category " and combined that with whatever was in cell A4 (in this case, 1), to give me the sheet name Pivot Category 1. But that is just the sheet name, we need to include a cell ref too, so thats where the &"'!A4" come in.

so INDIRECT("'pivot Category "&$A4&"'!A4") says...

use 'Pivot Category and combine it with 1 and then combine that with A4

Hope that makes some sense to you?

[baca25]

It all makes sense!
Took me a while, but I deciphered it. haha
Thanks again.