Share via

Pivot Table or Chart incorrectly counts a formula field

Anonymous
2015-11-09T10:47:12+00:00

In my excel file, I have data downloaded from an Internet online Form site where two pieces of data appear in the same filed. So the field looks like - Dynamic mattress Seating cushion.  It can contain either or both descriptions. In two separate column fields I used a formula to identify if the words appeared or not. So in a cell AD4 the formula used is '=IF(T4="","",IF(ISNUMBER(SEARCH($AD$3,B4)),"Yes",""))' My initial downloaded data is in cell B4 and I have a reference of the description in cell AD3. Cell T4 is just a delivery date.

So essentially its saying, If there is a delivery date entered in cell T4, check whether the words in cell AD3 are included in cell B4 and if so insert YES to cell AD4.

I use this formula for other fields as well, so I have two columns, one headed 'Dynamic mattresses and the other headed Seating cushions.

So, that all works fine. The issue is when I want to interrogate the data with a pivot table or pivot chart. You can see in the respective columns a series of blank cells or 'YES'.  When I create a pivot to show how many YES's for each type there are, it just counts all the fields that contain the formula. So, if there were 2 seating cushions and 4 dynamic mattresses, the chart shows there are 4 of each. Its like its counting the formulae and not the values.

Can anyone please help with advice.

Thankyou

Ron

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Rory Archibald 18,885 Reputation points Volunteer Moderator
2015-11-09T11:37:40+00:00

Unfortunately a count in a pivot table will count any cell that is not empty, and a cell containing a formula is not empty, even if the formula returns "".

I suggest you change the formula to return 1 for yes and 0 otherwise and then sum it instead of counting. If need be, you can apply a custom number format to show Yes in the cells with 1 using a format of:

"Yes";;;

Was this answer helpful?

3 people found this answer helpful.
0 comments

Answer accepted by question author

Rory Archibald 18,885 Reputation points Volunteer Moderator
2015-11-09T12:01:03+00:00

What formula did you use? Does it return 1 or "1"? The latter would be text and wouldn't be added.

Was this answer helpful?

2 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-11-09T12:43:22+00:00

    It was set as "1", but now changed to just 1.  This works brilliantly so many thanks for your help. Great job.

    Ron

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2015-11-09T12:58:15+00:00

    You're welcome. :)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-11-09T11:50:54+00:00

    Thanks, that answers why it wont count. I did not know that about Pivot Tables. However, having changed the results from Yes / No to 1 / 0 and using 'Sum'  it wont count the 1's as required.

    Was this answer helpful?

    0 comments