# Using IF to Count but eliminate some

#### Yusuf

##### Active Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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

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.

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

Replies
1
Views
557
Replies
3
Views
282
Replies
2
Views
662
Replies
2
Views
552
Replies
3
Views
290

1,196,042
Messages
6,013,049
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back