Google Sheets Import Data and COUNTIF?

0
votes

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

2
excelgoogle-sheetsformulacountif

2 Answers

0
votes

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"))))

0
votes

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")