Pivot tables - formula to count rows

Hi,

Last time I posted a question on here it was answered and resolved within 1 hour I am hoping for a similar result on the following :-

I have a pivot table that shows the Country and usernames in the Row.
Country Username Sales
England Joe 4
Adam 2
Sarah 4
England Total 10
France Antoine 3
France Total 3

What I need to do is count the number of usernames and not the total of the sales : So England Total should be 3 and France 1.

I have not been able to do that so another possibility would be to actually have a formula outside the pivottable to count the number of rows between England and England Total.

Can anyone offer a suggestion? As the number of rows can change I want the formula to 'search' if, for example, column A has "total" , take the left chars before "total" and search above until it matches and returns the number of rows.

Hope this makes sense.

left(a4,search("Total",a4)-2 would return "England" but how to find the occurance of England above A4

Someone will know how to do this so I await your answers

Many thanks in advance.

pivotrows.jpg

I hope this will help.

Try changing "count of Incident ID" to "count of Last name".

I've done it

On the screenshot, if I put the following formula into D19 I get the answer 14

=IF(ISERROR(LEFT(A19,SEARCH("Total",A19)-2)),"",ROW(A19)-MATCH(left(a19,search("Total",a19)-2),$a$1:$a$1000,FALSE)


Might be useful to other people too.