COUNTIF and conditional counting with text

[un1337]

Hi All,

This is my first post and I've been really impressed with the quality of responses to other questions, so here goes:

I have a big table (52 weeks wide and 60 locations high) of samples we need to take through the year over many different locations, such that the data looks like so:

Week |1 |2 |3 |4 |5
-------------|------|------|------|------|---
Location1 |A |A | |A |A
Location2 |A B |B |A |C |A C
Location3 |A C |B C |A |B |A B

So for example week 2, location 3 you take a sample of B and C, and the week after you just take A.

What I want to do count how many times A, B and C occur each week and place these totals at the bottom so that it looks like this:

Week |1 |2 |3 |4 |5
-------------|------|------|------|------|---
Location1 |A |A | |A |A
Location2 |A B |B |A |C |A C
Location3 |A C |B C |A |B |A B
.
.
.
---------------------------------------------
Total A |3 |1 |2 |1 |3
Total B |1 |2 |0 |1 |1
Total C |1 |1 |0 |1 |1

I have tried "=COUNTIF('range', "A")" and it works, but only if A occupies the cell on it's own, and it doesn't count the 'A C's and the 'A B's. Cells where I have two or more values contain the letter, seperated by a space.

Do you know of a way to do this? If you need clarification I'm happy to provide

[Aladin Akyurek]

On 2002-05-20 00:03, un1337 wrote:
Hi All,

This is my first post and I've been really impressed with the quality of responses to other questions, so here goes:

I have a big table (52 weeks wide and 60 locations high) of samples we need to take through the year over many different locations, such that the data looks like so:

Week |1 |2 |3 |4 |5
-------------|------|------|------|------|---
Location1 |A |A | |A |A
Location2 |A B |B |A |C |A C
Location3 |A C |B C |A |B |A B

So for example week 2, location 3 you take a sample of B and C, and the week after you just take A.

What I want to do count how many times A, B and C occur each week and place these totals at the bottom so that it looks like this:

Week |1 |2 |3 |4 |5
-------------|------|------|------|------|---
Location1 |A |A | |A |A
Location2 |A B |B |A |C |A C
Location3 |A C |B C |A |B |A B
.
.
.
---------------------------------------------
Total A |3 |1 |2 |1 |3
Total B |1 |2 |0 |1 |1
Total C |1 |1 |0 |1 |1

I have tried "=COUNTIF('range', "A")" and it works, but only if A occupies the cell on it's own, and it doesn't count the 'A C's and the 'A B's. Cells where I have two or more values contain the letter, seperated by a space.

Do you know of a way to do this? If you need clarification I'm happy to provide

=SUMPRODUCT((ISNUMBER(SEARCH("A",Range))+0)

where you can replace "A" with a cell ref.

[Ian Mac]

On 2002-05-20 00:03, un1337 wrote:
Hi All,

This is my first post and I've been really impressed with the quality of responses to other questions, so here goes:

I have a big table (52 weeks wide and 60 locations high) of samples we need to take through the year over many different locations, such that the data looks like so:

Week |1 |2 |3 |4 |5
-------------|------|------|------|------|---
Location1 |A |A | |A |A
Location2 |A B |B |A |C |A C
Location3 |A C |B C |A |B |A B

So for example week 2, location 3 you take a sample of B and C, and the week after you just take A.

What I want to do count how many times A, B and C occur each week and place these totals at the bottom so that it looks like this:

Week |1 |2 |3 |4 |5
-------------|------|------|------|------|---
Location1 |A |A | |A |A
Location2 |A B |B |A |C |A C
Location3 |A C |B C |A |B |A B
.
.
.
---------------------------------------------
Total A |3 |1 |2 |1 |3
Total B |1 |2 |0 |1 |1
Total C |1 |1 |0 |1 |1

I have tried "=COUNTIF('range', "A")" and it works, but only if A occupies the cell on it's own, and it doesn't count the 'A C's and the 'A B's. Cells where I have two or more values contain the letter, seperated by a space.

Do you know of a way to do this? If you need clarification I'm happy to provide

Try using the Wildcard * in your calculation.

=COUNTIF('range', "A*")

if the A may be second in any instance use

=COUNTIF('range', "*A*")

[un1337]

Thanks Ian for your help, that was the kind of thing I was looking for!

After putting the wildcard in like "*A*" it worked, but also counted things like "AG" which was a different code. Then I tried putting "*A *", "* A*" & "* A *" to stop the double counting.

This resulted in not recognising values that were at the start or the end of the cell as they didn't have adjacent spaces.

To fix this I recreated my original table and added a space at the start and end of every cell using the CONCATENATE command and pasting the values back in.

It now works a treat

Thank you Ian, and thank you Aladin for your input. I hope I can help out on this forum in the future. I'm sold.


Aruna Seneviratne