countifs.... help please

[LAURAAB1984]

Hi,

This is driving me insane....

I have this formula;

=SUM(COUNTIFS('[19_20 Consultations Master.xlsx]Consultations'!$L:$L,"Accept",'[19_20 Consultations Master.xlsx]Consultations'!$AB:$AB,{"#N/A"}))

Which is fine but as well as being an "Accept" in column L and an "#N/A" in column AB I need it to also count if they are "A" in a column on another spreadsheet;

=COUNTIF('[NR APP REPORT.xlsx]APP011 - Applications Details'!$R:$R,"A")

I need to merge these two formulas in order to count if the, in this case, student, meets all three criteria.

Hope this makes sense.

Thanks so much.

 

[tyija1995]

Hey, try this:

=SUM(COUNTIFS('[19_20 Consultations Master.xlsx]Consultations'!$L:$L,"Accept",'[19_20 Consultations Master.xlsx]Consultations'!$AB:$AB,{"#N/A"},'[NR APP REPORT.xlsx]APP011 - Applications Details'!$R:$R,"A"))

 

[LAURAAB1984]

Thank you but that doesn't work - it only seems to do the first two bits and not whether they are an "A" on the NR APP REPORT as well.

But thank you for answering.

 

[tyija1995]

Thank you but that doesn't work - it only seems to do the first two bits and not whether they are an "A" on the NR APP REPORT as well.

But thank you for answering.

Have you got your calculations set to manual on any of those workbooks? I'm pretty confident it should pickup all three criteria.
To check this: File -> Options -> Formulas -> Calculation options -> Set to automatic.

EDIT: Shortcut to File -> Options = (Alt+t+o) pressed in the same manner as you would press 123 (so no holding the alt)