Google Sheets Import Data and COUNTIF?

Question

I am using google sheets and trying to Import Data from another sheet into the current sheet, but looking for this imported data to pull certain values.

For example, I need to know how many incomplete assignments were submitted by males. This is what I have thus far...which is not working. I am getting the answer of 4 when I should be getting 2. "Incomplete" is in column A, Male is in K

For example: =COUNTIF(IMPORTRANGE("Key","Sheet!A:A"),"=Incomplete")*(COUNTIF(IMPORTRANGE("Key","Sheet!K:K"),"=Male"))

Also, I need to extend this further and be able to calculate how many students had an excuse for not completing the assignment. I do not need to know the actual reason, I just need to know if there was an excuse. Not applicable is an option, but needs to be ignored. This is what I have thus far, but it will only recognize the first answer, not the rest.

=COUNTIFS(IMPORTRANGE("Key","Sheet!N:N"),{"Absent";"Illness";"Social Issues";"The Dog Ate It";"On Holidays";"Not Interested";"Bored"})

Any assistance greatly appreciated.

Billy

Ed Nelson · Answer 1 · 2015-11-16T15:35:14.450

0

There may be a better way, but this works:

=COUNTIF(IMPORTRANGE("Key","Sheet!N:N"), "Illness")
+ COUNTIF(IMPORTRANGE("Key","Sheet!N:N"), "Social Issues")
+ COUNTIF(IMPORTRANGE("Key","Sheet!N:N"), "The Dog Ate it")
+COUNTIF(IMPORTRANGE("Key"),"Sheet!N:N"), "On Holidays")
+COUNTIF(IMPORTRANGE("Key","Sheet!N:N"), "Not Interested")
+COUNTIF(IMPORTRANGE("Key","Sheet!N:N"), "Bored")
+COUNTIF(IMPORTRANGE("Key","Sheet!N:N"), "Absent")

Sorry, I forgot the other part.

=COUNTIFS(IMPORTRANGE("Key","Sheet!A:A"), "Incomplete",IMPORTRANGE("Key","Sheet!K:K"),"Male")

edited Nov 16 '15 at 15:35

answered Nov 15 '15 at 16:56

Ed Nelson

10,015
2
27
29

Hi, Thank you for the reply. This formula reads the illness, but not the others in the list, ?? any ideas? – Billy Nov 16 '15 at 09:00
Copy the complete formula as one. All 7 lines together. – Ed Nelson Nov 17 '15 at 01:56

score 0 · Answer 2 · answered Nov 15 '15 at 18:53

0

For the males and incompletes, you can use a filter view:

=COUNTA(FILTER(IMPORTRANGE("Key","Sheet!A:A"),IMPORTRANGE("Key","Sheet!K:K")="MALE",IMPORTRANGE("Key","Sheet!A:A")="INCOMPLETE"))

To return counts of excuses on top of this, you can add the istext() criteria to the existing filter:

=COUNTA(FILTER(IMPORTRANGE("Key","Sheet!A:A"),IMPORTRANGE("Key","Sheet!K:K")="MALE",IMPORTRANGE("Key","Sheet!A:A")="INCOMPLETE",ISTEXT(IMPORTRANGE("Key","Sheet!N:N"))))

answered Nov 15 '15 at 18:53

pleeyomi

11

Hi, thanks for the reply. These formulas read the first time it sees the incomplete in column A, but not there after. Incomplete will appear several times down the sheet but will reference to a particular cell in regards to the outcome. For example. A38 = incomplete, k5 - male, then further down the spreadsheet. A98=incomplete, k69 = male, this repeats 10 times down the page. Any assistance greatly appreciated. – Billy Nov 16 '15 at 09:01
Sometimes there are error issues when you're importing a range multiple times in a formula. Why that is - I have no idea! Do you mind sharing the sheet so I can take a look? – pleeyomi Nov 17 '15 at 19:44

Google Sheets Import Data and COUNTIF?

2 Answers2