Using IF to Count but eliminate some

[Yusuf]

Hi all

I need to count data from a two Pivot tables that's constantly being refreshed.
Two tables below each other because the data I have excedes 25 000 rows and the table doesn't seem to accomodate that amount.

The problem I have is that it also counts headings and totals
Eg, Grand Total and Account Number (in the second table)

The return should be "Incomplete" if there's no data alongside the Acc number


IF however you have any tips on how I can make use of only one Pivot table for all the data then I won't need this formula and would be extremely greatful

 

[texasalynn]

not sure I understand. But a pivot table can read from more than 25000 rows. I do one each month off of data that has 32,000+ rows. Could you tell a little more.

But if you have to do it that way, couldn't you have your count formula then just subtract the headings. Something like this perhaps?

=counta(b2:b100)-2

HTH

 

[Yusuf]

I wish it were that simple

The Pivot msg reads, (when I select the Acc numbers for the Row Fields)
"A field in your source data has more unique items than can be used in a Pivot table. Microsoft Excel may not be able to create the PivotTable, or may create the PivotTable without the data from this field"

I have written a formula beside the table that states
=IF(B4&C4="","Incomplete","") (..dragged down)
This is what I'm counting to find completed acc's.

NB - Sometimes there are blank cells in either Col's B & C which means I wouldn't get an accurate total.

 

[texasalynn]

so why can't you use your formula and put the -2 behind it?

Without see a sample of your data I'm not able to picture what you are saying