I’m trying to use COUNTIF to count the number of specific characters in set of a row

Question

Level 1

4 points

I’m trying to use COUNTIF to count the number of specific characters in set of a row

I have a section of a table above that I’d like to create a separate table to count the number of “H” I have for each individual within the range of columns that have “Jan” or “1”.

I can easily do a COUNTIF function and highlight the row for each day in “Jan” and tally up the number of times, say “H” shows up for a specific individual and copy that function to each individual. However, I want to be able to lookup the name of an individual and perform the count for that individual within all the columns that have “Jan”

I don’t know if that is a nested COUNTIF, VLOOKUP, HLOOKUP or some other way of doing it but I can’t make a function that will work. Can anyone help?

Posted on Aug 26, 2023 3:31 PM

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,633 points

Aug 28, 2023 10:16 AM in response to sadiqkabiri

Paste this in C4 and propagate to all cells below and to the right.

=IFERROR(COUNTIFS(OFFSET(By day::$A$5,XMATCH($A4,By day::$A),1,1,COLUMNS(By day::$1:$1)−1),"H",By day::$4:$4,C$3),"")

To work properly the row with CALL CENTER in it must be part of the header. This allows proper sorting of column 1 by names in both tables, as well as moving rows by dragging. Otherwise rearranging rows could have disastrous consequences.

This formula doesn't use the Month row with the numbers, so you can remove it if you want, but only after entering these formulas. However make sure that Jan, Feb, etc. are of the same type, date or text, in both tables because the comparison hinges on that.

The formula is built to allow unlimited expansion of the tables horizontally and vertically without the need to rework it. It self-adjusts to the size of the tables.

Explanation of the formula:

1- find on which row in column A of the By day table is the name of the person (XMATCH)

2- define a range in the By day table that includes that whole row (OFFSET)

3- count how many H's there are in that range, where the month in both tables is the same (COUNTIFS)

Reply

Answer 2

SGIII

Level 8

37,532 points

Aug 26, 2023 3:42 PM in response to sadiqkabiri

Not sure exactly what you are trying to do but it sounds as if the function you want is COUNTIFS.

COUNTIFS should allow you to count cells where month is Jan AND individual is a specific individual.

More on COUNTIFS here.

COUNTIFS - Apple Support

COUNTIFS(test-values, condition, test-values…, condition…)

SG

Reply

Answer 3

sadiqkabiri Author

Level 1

4 points

Aug 28, 2023 5:21 AM in response to SGIII

I will try and write it algorithmic to explain what I’m trying to achieve.

I have TABLE1 with the rota of all the staff that goes across the whole year.

TABLE2 shows the holiday entitlement for all the staff from TABLE1 and how many holidays they have taken each month.

Everytime I put a “H” on TABLE1 corresponding to the staff TABLE2 should update for that month.

I want to make a function on TABLE2 in one cell and then copy that function across all cells.

I want to isolate the row and column to perform the count in.

The COUNTIFS function does not perform what I’m intending to do. I tried it

Reply

I’m trying to use COUNTIF to count the number of specific characters in set of a row

Similar questions