Wednesday, August 17, 2011

yogi_Sum Values From Rows Different Than The Corresponding Matching Row

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

ryangibsonstewart said:
How do I use QUERY to return a different row than specified?
1 | Patrols & | Youth &
| Troops | Adults
2 | Patrol A | 10
3 | 13 |
4 | |
5 | Patrol B | 2
6 | 13 | 4
7 | |
8 | Patrol C | 7
9 | 57 | 2
10| |
The numbers under the patrol names represent Boy Scout Troop numbers. The top numbers (next to the patrol names) are the number of youth that will be assigned to the patrol. The bottom number (next to the Troop number) is the number of adults that will be assigned to the patrol. (I know this isn't the best way to set up a spreadsheet, but this works visually for patrol assignments, and this is the way that the 100 or so Troop leaders understand the information.)
I want to be able to count the total number of youth and adults from a given Troop. So I want to be able to return the numbers:
Troop 13: 12 youth
Troop 13: 4 adults
Troop 57: 7 youth
Troop 57: 2 adults
I'm stuck. I tried using the QUERY formula, but I could only get it to return numbers in the same row (meaning the adult numbers, not the youth numbers). I also couldn't get it to SUM any numbers. Hmm.
Any help would be greatly appreciated. Thanks!
FYI, I use Google Chrome, on Windows 7.
here is one way ...