# Formula needed: Count based on unique values

#### ThomasBrandt08

##### New Member
I basically have a spreadsheet, where the first 4 columns contain unique values (such as different species of trees and soil types) (and only one values in each cell.)
The following columns should list how many times each type of value occur, for example - there is a column that should count the total number of trees listed in the first 4 columns, and there is a column that should count how many times soils are listed, and so on.

Let me further describe what I need with the use of the trees example:
Column 1: pine species
Column 2: redwood species
Column 3: green grass
Column 4: grey sand
Trees column: HERE WE WANT IT TO COUNT THE 2 TREES AND LIST THE NUMBER 2

The formula should therefore look at column ONE and count a 1 if a tree is listed OR a 0 if something else is listed.
Then it should look at column TWO and add a 1 if a tree is listed OR a 0 if something else is listed - and add this number to what it found from column ONE
Then it should look at column THREE and add a 1 if a tree is listed OR a 0 if something else is listed - and add this number to what it found from column TWO
Then it should look at column FOUR and add a 1 if a tree is listed OR a 0 if something else is listed - and add this number to what it found from column THREE

Finally, the formula should place the total value (from 0 to 4) in the "Trees" column.

Thank you in advance for any help.

Cheers,
Thomas

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Care to post a 5-row sample along with the desired results?

column 1--------column 2--------column 3--------column 4--------PV--------NPV--------trees--------grass--------herb--------soil
pine tree--------green grass-------green herb-----grey soil--------3----------0------------1------------1-------------1-----------1
dry grass--------redwood tree----------------------------------------1----------1------------1------------0------------0------------0
green grass--------pine tree--------red soil--------------------------2----------0------------1------------1------------0------------1

PV is photosynthetic vegetation - green vegetation
NPV is non photosynthetic vegetation - dry or dead vegetation

Please excuse the format, wish there was a straight forward way of inserting a screen-shot.

Thank you!

column 1--------column 2--------column 3--------column 4--------PV--------NPV--------trees--------grass--------herb--------soil
pine tree--------green grass-------green herb-----grey soil--------3----------0------------1------------1-------------1-----------1
dry grass--------redwood tree----------------------------------------1----------1------------1------------0------------0------------0
green grass--------pine tree--------red soil--------------------------2----------0------------1------------1------------0------------1

PV is photosynthetic vegetation - green vegetation
NPV is non photosynthetic vegetation - dry or dead vegetation

Please excuse the format, wish there was a straight forward way of inserting a screen-shot.

Thank you!

Let A1:J5 house the data and processing areas, the headers included.

E2, just enter and copy down:

=SUM(G2:I2)-F2

F2, just enter and copy down:

G2, just enter, copy across, and down:

=COUNTIF(\$A2:\$D2,"*"&G\$1&"*")

Hope this suffices to cover the required categorizations.

pls clear the data through excel sheet

Let A1:J5 house the data and processing areas, the headers included.

E2, just enter and copy down:

=SUM(G2:I2)-F2

F2, just enter and copy down:

G2, just enter, copy across, and down:

=COUNTIF(\$A2:\$D2,"*"&G\$1&"*")

Hope this suffices to cover the required categorizations.

Edit:

Change trees in G1 to tree.

Perfect, exactly what I needed.

Thank you very much!!

Perfect, exactly what I needed.

Thank you very much!!

You are welcome. Thanks for providing feedback.

Replies
1
Views
162
Replies
4
Views
529
Replies
1
Views
132
Replies
14
Views
230
Replies
8
Views
118

1,203,642
Messages
6,056,507
Members
444,872
Latest member
Vishal Gupta

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