Translating COUNTIFS in Excel to Google spreadsheets

[Silha83]

Hello all,

I have used quite a few COUNTIFS formulas in Excel that do not translate into Google spreadsheets. I was wondering if anyone would know how to translate the following formula into Google spreadsheets:

=(COUNTIFS(Permanent!H:H,"=Onsite",Permanent!B:B,"=London"))+(COUNTIFS(Freelance!J:J,"=Onsite",Freelance!B:B,"=London"))

I have two spreadsheets (viz. Permanent & Freelance) and in the above formula I want to calculate the sum total of staff members in London who are onsite.

Any help would be appreciated.

Thank you!

 

[pgc01]

Hi
Welcome to the board

Try:

=SUMPRODUCT((Permanent!H1:H1000="Onsite")*(Permanent!B1:B1000="London"))+SUMPRODUCT((Freelance!J1:J1000="Onsite")*(Freelance!B1:B1000="London"))

Adjust the number of rows if 1000 is not enough.

 

[Silha83]

Thank you so much!

I tried that but it doesn't return the correct results even when I change the cell range. I read online that you can use array formulas in Google spreadsheets to transfer COUNTIFS from Excel. Would you know about this?

 

[circledchicken]

Hi,

You could also try out the new version of Google Sheets that supports COUNTIFS.
I haven't tried it myself but see:
https://support.google.com/drive/answer/3541068?hl=en&ref_topic=20322
https://www.youtube.com/watch?v=9AyoRkr4I3U

 

[barry houdini]

I read online that you can use array formulas in Google spreadsheets to transfer COUNTIFS from Excel. Would you know about this?

Try this version

=Arrayformula(Sum(If(Freelance!J:J="Onsite",IF(Freelance!B:B="London",1))))

 

[Silha83]

Yes, I have tried to update to the new spreadsheets but I think there might be some restrictions on my account or version control as it is a work gmail account, so I can't access the update.

 

[Silha83]

Ooh, thanks Barry Houdini! Only problem is, I want the sum total of onsite Londoners in both the freelance tab (which your formula includes) and also in the permanent tab. Is there a way I can do this?

 

[barry houdini]

Just add two together like your original, i.e.

=Arrayformula(Sum(If(Permanent!H:H="Onsite",IF(Permanent!B:B="London",1)))+Sum(If(Freelance!J:J="Onsite",IF(Freelance!B:B="London",1))))

 

[Silha83]

Awesome, perfect and thank you, Barry - it worked a treat!