Excel Count specifc value across range of columns, but only once per column

Question

I have been trying to work out how to count the number of times a value appears across a range, but only once per column.

Using the example data below, the results I want to get to are shown in the end cells. Using the =COUNTIF(A3:G5,J3) counts every time a name appears, causing double-ups on some days.

How can I make it count each name once per column, but across multiple columns (there would be 1 column for each day of the year in the real data)

Thanks

Gary's Student · Accepted Answer · 2020-09-07 13:24:38Z

3

in A7 enter:

=IF(COUNTIF(A3:A5,$J$3)>0,1,0)

and copy this across to G7. then in H7 enter:

=SUM(A7:G7)

answered Sep 7, 2020 at 13:24

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deaddingo Over a year ago

Awesome. This works perfectly and I would have never thought of doing it this way. Many thanks.

basic · Accepted Answer · 2020-09-07 14:26:51Z

2

You also can count values using single array formula:

=SUM(--(MMULT(TRANSPOSE(--($A$2:$G$4=I2)),ROW($A$1:$A$3)^0)>0))

Array formula after editing is confirmed by pressing ctrl + shift + enter

answered Sep 7, 2020 at 14:26

basic

12k2 gold badges15 silver badges29 bronze badges

2 Comments

deaddingo Over a year ago

WOW. That is awesome. It might be a silly question, but why is the ROW a shorter range than the lit of people? I ran a test and had the ROW as the the full list and it seemed to work, but just wanted to check.

basic Over a year ago

@deaddingo This is because the MMULT function requires that the second range have as many rows as the first range columns. After TRANSPOSE range A2:G4 becomes 3 columns x 7 rows, respectively the second argument must have three rows.

Spencer Barnes · Accepted Answer · 2020-09-07 13:27:09Z

0

So the final question is 'count how many columns the name appears in'?

In which case you need a hidden row at the top or bottom of the sheet that contains everything in that column. Say it's row 1:

a) The formula in A1 would be =CONCAT(A3:A5), B1 would be =CONCAT(B3:B5) and so on (where the range covers rows 3 to 5). CONCAT sticks text strings together.
b) the cell doing the counting would be =COUNTIF(A1:G1, "*J3*"). What that does is check which cells in A1:G1 contain J3 among other things.

And voila, problem solved.

answered Sep 7, 2020 at 13:27

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

1 Comment

Spencer Barnes Over a year ago

@Garysstudent's answer works just as well - it's another way of summing up each column separately and then doing the overall count.

Collectives™ on Stack Overflow

Excel Count specifc value across range of columns, but only once per column

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related