CODEWITHSUNDEEP
arraysexcelcountgoogle-sheetsinstance
Aaron
Aaron

Reputation: 33

Google Sheets/Excel: Counting values in a range that changes with each row, across multiple rows?

       A            B              C             D             E
 +--------------------------------------------------------------
1| Red          Blue           Dark Green    Blue
2| Light Blue   Red            Blue          Red
3| Blue         Black          Dark Green
4| Light Blue   Light Blue
5| Dark Green
6| Blue         Red            Green         Black         Blue
7| Dark Green   Blue

So what I'm trying to accomplish is to count how many times a value (say, 'Blue') appears between the First and Last cells that contain any value in a set of rows.

Using the above example the result should be '2' for 'Blue', where by 'Blue' can be found '6' times across the whole range, but I want to exclude all instances that appear in the first and last positions of any given row.

I wish I had an example of the formula I've been using so far, but I haven't been able to produce anything that works properly. :(

Upvotes: 1

Views: 66

Answers (2)

sylenix
sylenix

Reputation: 161

Are you trying to do it like this?

enter image description here

The bottom formula for column A is:

=COUNTIF(A2:A6,"Blue")

That will count any "Blue" occurrences in the A2:A6 range. You can just copy that formula to the rest of the columns. Notice that I excluded the topmost (A1:D1) & bottommost (A7:D7) part for each column as per your instruction to avoid it.

If you want to count the occurrences all at once, you can do it like so:

=COUNTIF(A2:E6,"Blue")

Upvotes: 0

newacc2240
newacc2240

Reputation: 1425

=SUMPRODUCT(($A$1:$D$5="Blue")*NOT(COLUMN($A$1:$D$5)=1)*NOT($B$1:$E$5=""))

Three parts in the SUMPRODUCT

  1. ($A$1:$D$5="Blue") Checks the values of each cells whether they match the target or not.

  2. NOT(COLUMN($A$1:$D$5)=1) The cell should not be in column #1. However this can be omitted if you use the range B1:D5.

  3. NOT($B$1:$E$5="") If the cell is the last one of that row, the next cell would be a blank. Therefore we check the following cells are blank or not.

Upvotes: 1

Related Questions