Pivot Table or VBA - Counting repetitive instances in multiple columns

[hoopdy]

Hello All - My first time

I have data similar to the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Column 1[/TD]
[TD="align: center"]Column 2[/TD]
[TD="align: center"]Column 3[/TD]
[TD="align: center"]Column 4[/TD]
[/TR]
[TR]
[TD]Robin[/TD]
[TD]Ostrich[/TD]
[TD]Dove[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ostrich[/TD]
[TD]Sparrow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dodo[/TD]
[TD]Robin[/TD]
[TD]Sparrow[/TD]
[TD]Eagle[/TD]
[/TR]
[TR]
[TD]Ostrich[/TD]
[TD]Dove[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sparrow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Robin[/TD]
[TD]Sparrow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dodo[/TD]
[TD]Robin[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ostrich[/TD]
[TD]Dove[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Robin[/TD]
[TD]Sparrow[/TD]
[TD]Eagle[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dove[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 335"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need to produce a table that shows the total instances of each type of bird from all four columns such that:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Bird[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD]Dove[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Robin[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Ostrich[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Sparrow[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Eagle[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Dodo[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]21[/TD]
[/TR]
</tbody>[/TABLE]

I tried to create a pivot table, but it only counts the first column, or includes the last three each as a subcategory of the column right before it.

Does anyone know how to do this? Preferably using a pivot table, but, also with VBA if necessary.

Thank you in advance.

 

[SpillerBD]

You do not have a table nor an array since there is nothing that conforms to the columnar fields.
However, you can still use COUNTIFS.
And yes, you would have to provide the list or reorganize your values to provide the condition.

 

[hoopdy]

You do not have a table nor an array since there is nothing that conforms to the columnar fields.
However, you can still use COUNTIFS.
And yes, you would have to provide the list or reorganize your values to provide the condition.

Thank you, but I am relatively new to this. Would you or someone else please explain in more detail?

 

[SpillerBD]

In Excel, data is stored in table arrangement where a column represents a field and a row represents a record. Each record would have a value in each field.
The relationships is by the intersections of Columns and Rows.
What you have shown is a blob.
A PivotTable MUST have its data arranged in some Tabular method.
IF you have a list of all the possible birds to be counted, you can use that list and the COUNTIFS function to give your Totals.

I'm sure there is VBA to capture the Array of Values and give just a list from Which a Pivot Table could be generated. Mike Girvin might have something in a formula method, but that could be a big array (large number of values.)

Question. The sheet you plan to tabulate is only going to have the names of things that you want counted the occurrence of?

 

[hoopdy]

In Excel, data is stored in table arrangement where a column represents a field and a row represents a record. Each record would have a value in each field.
The relationships is by the intersections of Columns and Rows.
What you have shown is a blob.
A PivotTable MUST have its data arranged in some Tabular method.
IF you have a list of all the possible birds to be counted, you can use that list and the COUNTIFS function to give your Totals.

I'm sure there is VBA to capture the Array of Values and give just a list from Which a Pivot Table could be generated. Mike Girvin might have something in a formula method, but that could be a big array (large number of values.)

Question. The sheet you plan to tabulate is only going to have the names of things that you want counted the occurrence of?

This was an example of a small portion of the data in the sheet (I cannot post the actual data).

I figured it out using a query and then "unpivot" on the columns represented by my example table, incorporating that back into the workbook, and creating my pivot tables from that ...

Thanks for your clarifications.