Excel query - count number of times a word appears in a grid

barneysplash

Hi all

I have an Excel query, if anyone could help me with it, I would be much obliged.
I have a set of initials in a grid:

AA AB AC
AA AB AC
AA AB AC
AA AB AC
AA AB AC
AA AB AC
AV AN AC

I want to use an Excel formula to give a report of the number
of instances of each entry, for example:

AA 5
AB 5
AC 6
AN 1
AV 1

If I put the data into a single column, I can do this with a pivot table.
But there is a lot of data either side of the entries, also it takes
ages to format the each column into a single column that is suitable for
pivot table work.

I'd like to be able to select the data and use a formula to do the counting.
Does anyone know a formula that will do this?

Thanks
Barney

Capt'n Midnight

a generic way would be to use COUNTIF

https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34

barneysplash

Capt'n Midnight wrote: »

a generic way would be to use COUNTIF

https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34

Thanks for the suggestion, but I tried COUNTIF, it's not what I'm looking for.

Each time the list is provided, different characters are used in each "AZ" entry.

To get the total number of entries in list is easy with a pivot table, but the data comes to me in grid formations, and not a straight list. This makes
pivot tables clumsy, and I don't want to edit the source data, if I can avoid it.

Thanks again