Formula needed: Count based on unique values

ThomasBrandt08

New Member
Joined
Oct 29, 2011
Messages
3
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,428
Messages
6,172,043
Members
452,444
Latest member
ShaImran193

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top