Converting a Microsoft Excel formula to a Google Doc formula isn’t necessarily the hardest thing in the world, but they aren’t the easiest either if you don’t know what you are looking for. I’ve been using Google Docs for a couple of months now and have found that there are some large differences with some of the formulas between Google Spreadsheets and Microsoft Excel.

If you read my previous article on some Awesome Google Spreadsheet Formulas, I will leverage some of these formulas to assist in some of the conversions. So while this isn’t a complete list of Microsoft Excel to Google Spreadsheet formula conversions, I’ll hope that you can find some use with it since these are some very common formulas.

The formulas below are converted from Microsoft Excel to Google Spreadsheets, but obviously you can reverse the logic and perform the same conversion. Also, just like any other of these post types I write, as I convert more formulas I’ll just update this article and add to it. Enjoy!
[center-ad]

Convert Microsoft Excel Formula =CountIFS() to Google Doc Formula

This formula counts the number of cells specified by a given set of conditions or criteria. This formula can count values in a range with multiple conditions, which is what makes it so much more valuable then the common {c}=Count(){/c} function.

Syntax / Usage:

MS Excel: {c}=CountIFS(range1, criteria1, [range2, criteria2]…){/c}
Google: {c}=CountA(Filter(count_range;range1[operator][criteria1];range2[operator][criteria2];…)){/c}

Example:

MS Excel: {c}=CountIFS(B2:B100,”Yes”,C2:C100,”No”){/c}
Google: {c}=CountA(B:B;B2:B100=”Yes”;C2:C100=”No”){/c}

Convert Microsoft Excel Formula =AverageIFS() to Google Doc Formula

This formula finds an average(arithmetic mean) for the cells specified by a given set of conditions or criteria. This formula can calculate an average against a range with multiple conditions, which is what makes it so much more valuable then the common {c}=Average(){/c} function.

Syntax / Usage:

MS Excel: {c}=AverageIFS(avg_range,range1,criteria1,range2,criteria2…){/c}
Google: {c}=Average(Filter(avg_range;range1[operator][criteria1];range2[operator][criteria2];…)){/c}

Example:

MS Excel: {c}=AVERAGEIFS(B2:B100,B2:B100,”>10″,B2:B100,”<100"){/c}
Google: {c}=Average(Filter(B2:B100;B2:B100>10;B2:B100<100)){/c}

Convert Microsoft Excel Formula =SumIFS() to Google Doc Formula

This formula adds the cells specified by a given set of conditions or criteria. This formula can calculate a sum against a range with multiple conditions, which is what makes it so much more valuable then the common {c}=Sum(){/c} function.

Syntax / Usage:

MS Excel: {c}=SumIFS(sum_range, range1, criteria1, range2, criteria2, …){/c}
Google: {c}=Sum(Filter(sum_range;range1[operator][criteria1];range2[operator][criteria2];…)){/c}

Example:

MS Excel: {c}=SumIFS(A1:A100,B1:B100,”>0″,C1:C100,”<10"){/c}
Google: {c}=Sum(Filter(A1:A100;B1:B100>0;C1:C100<10)){/c}

Convert Microsoft Excel Formula =Substitute() to Google Doc Formula

This formula replaces part of a string with a different text string. This formula is pretty easy to convert, the kicker is just that the order of operation is slightly different between MS Excel and Google Docs so be aware.

Syntax / Usage:

MS Excel: {c}=Substitute(search_text,old_text,new_text,instance_num){/c}
Google: {c}=Substitute(old_text,search_text,new_text,occurrence)){/c}

Example:

MS Excel: {c}=Substitute(“this is just a test”,”test”,”sample”,1){/c}
Google: {c}=Substitute(“test”,”this is just a test”,”sample”,1){/c}

If there are any other formulas that you need me to convert just let me know and I’ll get them converted and updated here on this post. Otherwise, I’ll just keep this one rolling as I continue to discover. Cheers!