Google Sheet Query Count working, but other functions are not (e.g., sum, avg)

Question

I want to count / sum / average various data points based on many criteria.

One of the criteria at least one of the touchpoints falls between two set dates. There are five potential touchpoints. Each potential touch point has a column.

I created this query that works with count

`=QUERY(B10:G,"Select count (B) 
where B<5 AND 
(C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"')
label count(B) ''", 0)`

However, when I use other funchtions it does not work

`=QUERY(B10:G,"Select Sum(B) 
where B<5 AND 
(C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"')
label count(B) ''", 0)`

This is what the data looks like: https://docs.google.com/spreadsheets/d/1BiWsm6uPncPsdM7WiVDdRSbkUcbulNLhITqbt4rqXB8/edit?usp=sharing

Start   End         
1/1/23  1/31/23         
                

Number	Screening	F1	F2	F3	F4
1	1/1/23	1/4/23	1/6/23	1/8/23	1/10/23
2	12/03/22	1/4/23	02/05/23	3/9/23	04/10/23
3	1/7/23	1/8/23	1/9/23	1/10/23	1/11/23
4	09/22/22	10/28/22	12/03/22	1/10/23	02/17/23

I searched this site and tried other formulas

Answer 1

Check the Label Statement

In your second formula where you wanted to get the sum of values in column B, change the label count(B) to label sum(B). Thus, the formula should look like:

For Sum:

=QUERY(B10:G,"Select Sum(B) 
where B<5 AND 
((C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"'))
label Sum(B) ''", 0)

For Avg:

=QUERY(B10:G,"Select Avg(B) 
where B<5 AND 
((C >= date '"&TEXT(C9, "e-m-d")&"'AND C <= date '"&TEXT(D9, "e-m-d")&"')
Or (D >= date '"&TEXT(C9, "e-m-d")&"'AND D <= date '"&TEXT(D9, "e-m-d")&"')
Or (E >= date '"&TEXT(C9, "e-m-d")&"'AND E <= date '"&TEXT(D9, "e-m-d")&"')
Or (F >= date '"&TEXT(C9, "e-m-d")&"'AND F <= date '"&TEXT(D9, "e-m-d")&"')
Or (G >= date '"&TEXT(C9, "e-m-d")&"'AND G <= date '"&TEXT(D9, "e-m-d")&"'))
label Avg(B) ''", 0)

The same concept is applicable to other functions (i.e. min, max, etc.) Always take note that when using these formulas, you should also take a look at the label.