View original (French)

NB.SI with non-contiguous cells

sputnicbe Posted messages 5 Status Member -  
 Estelle -
Hello,
I need to count the cells containing a criterion on cells that are not contiguous, so not in a range like A1:A20 but A1;A3;A7;
When I build my formula COUNTIF(A1;A3;A7;"criterion") Excel tells me that too many arguments have been entered. How should I go about it?

The file in question is a daily attendance management.
Vertically is the list of people.
Horizontally: the days of the month with 3 columns for each day. arrival time, departure time, total attendance (with formula).
I need to make a statistic of attendance (by person) based on 3 criteria: <3h, between 3h and 5h, >5h.

It's not the criteria that bothers me but this issue of excess arguments in the analysis range. Thank you in advance and I wish you ... good day, good afternoon, or good night.

1 answer

Answer 1 / 1
chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
 
Hello,

the formula for COUNTIF is used as follows:
=COUNTIF(range;criteria).

If you want to use multiple ranges, you will need to use:
=COUNTIFS(criteria_range1;criteria1;[criteria_range2;criteria2];...)

Best regards.
--
This guy wasn't the brightest, as he was foolish.
-1
sputnicbe Posted messages 5 Status Member
 
Here is what I tried

=COUNTIFS(E6,"<3",[H6,"<3"])

but I get back "Invalid name" and it selects H6.
0
chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
 
In fact, the brackets mean that criteria range #2 is optional. I should have specified that.
Try again without the brackets.
0
sputnicbe Posted messages 5 Status Member
 
Now I have a result but it's not the right one. It shows "1" even though the content of the two cells is >3.
I specify that the format of these two cells (E6 and H6) is formatted in hour:minute (I also need to calculate the total daily presence).


I just tried this:

=COUNTIFS(p_enfant1,"<3")

by selecting with CTRL all the cells for which I want to do my statistics and then giving them a name range. But I get a #VALUE! message.

I continue my quest.
0
sputnicbe Posted messages 5 Status Member
 
Am I mistaken or is this criteria COUNTIFS formula to be used when ALL criteria must match?
0
Estelle
 
The solution is: =COUNTIF(E6,"<3") + COUNTIF(H6,"<3") + ....
The COUNTIFS formula will only count 1 if a number <3 appears simultaneously in your different non-adjacent ranges.
1