Formula needed: Count based on unique values

[ThomasBrandt08]

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

 

[Aladin Akyurek]

Care to post a 5-row sample along with the desired results?

 

[ThomasBrandt08]

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
brown soil--------oak tree--------green herb--------dead tree------2----------1------------1------------0------------1------------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!

 

[Aladin Akyurek]

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
brown soil--------oak tree--------green herb--------dead tree------2----------1------------1------------0------------1------------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:

=SUM(COUNTIF($A2:$D2,"*"&{"dry","dead"}&"*"))

G2, just enter, copy across, and down:

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

Hope this suffices to cover the required categorizations.

 

[jalanankur]

pls clear the data through excel sheet

 

[Aladin Akyurek]

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:

=SUM(COUNTIF($A2:$D2,"*"&{"dry","dead"}&"*"))

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.

 

[ThomasBrandt08]

Perfect, exactly what I needed.


Thank you very much!!

 

[Aladin Akyurek]

Perfect, exactly what I needed.


Thank you very much!!

You are welcome. Thanks for providing feedback.