Google Sheet Formula - Pull Specific Data from Multiple Sheets and Calculate SUM Corresponding To Those Lines

Ryneff 0
Viewed 36

I am attempting to write two formulas for google sheets:

One formula searches for if a value is present a cell range on one tab (say column 1, tab 1 - sheet = "American Birds") and is also present in a cell range on another tab (say column 1, tab 2 - sheet = "Australian Birds") and will present that value

The second formula searches for if a value is present a cell range on one tab (say column 1, tab 1 - sheet = "American Birds") and is also present in a cell range on another tab (say column 1, tab 2 - sheet = "Australian Birds"), then it will refer to the corresponding column 2 count for both, and give the SUM. (Note, the same item may appear on multiple lines of either sheet).

I am sure the first will utilize an IF or IMPORTRANGE function while the second will need to utilize the SUM or LOOKUP formula, but I am honestly not even sure where to start (am not super versed in spreadsheet formulas). Hypothetically, the second formula could also just search for the value of column 1, tab 3 (sheet = "Total Birds") and pull a total count from tab 1 and 2 if present. Or, to make that formula easier, I could just do one tab at a time, for column 2 and 3 (corresponding to tab 1 and 2 respectively) and have column 4 be a simply SUM of column 2 and 3.

I have attached a mock spreadsheet I made for reference on this ask. Any help is greatly appreciated!

https://docs.google.com/spreadsheets/d/1yxamznpoTXpd73Rs9NJkpMU-BdbvCJ5mYabUWcWB650/edit#gid=943657988

formula spreadsheet google-sheets-formula google-sheets
1 Answers

use:

={QUERY({
 QUERY('American Birds'!A:B,   "select A,B,'American Birds Total'", ); 
 QUERY('Australian Birds'!A:B, "select A,B,'Australian Birds Total'", )}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 pivot Col3 label Col1'Birds'", ), 
 QUERY({'American Birds'!A:B; 'Australian Birds'!A:B}, 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)'Overall Bird Total'")}

enter image description here

player00
Related