yogi_QueryFunctionWithColumnAndRowCriteria

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

parefr said ...
I am trying to use the filter function with a condition base on a colum and a condition base on a row. The data look like this:


Row
1 Item Color Day1 Day2 Day3
2 AAA   Red     5    0    3
3 AAA   Blue    1    2    4
4 AAA   Yellow  6    2    1
5 BBB   Black   0    2    3
6 BBB   Red     7    6    2


The results I would like is:


Row
9  Item Day1 Day2 Day3
10 AAA   12     4    8
11 BBB    7     8    5
------------------------------------------------


In the following solution I have used the QUERY function

parefr commented ...
I see what your query is doing, but I will have to change the query every time I add a new day. Is there another way to do it?


Yes we can change the range from A1:E6 to A:E ... I have added Sheet2 to illustrate this -- the formula in cell G1 is ...
=query(A:E,"select A,sum(C),sum(D),sum(E) where A<>'' group by A label sum(C) 'Day1', sum(D) 'Day2', sum(E) 'Day3' ")