# Converting Unique,Filter,Countif formula in Google Sheets to Excel

1. ## Converting Unique,Filter,Countif formula in Google Sheets to Excel

Hi,
I have a list of students (by ID#) with Ds,D+, and Fs. I need a formula to organize them into 3 categories: Only 1 D or D+, >1D or D+, 1 or more Fs. Students can only be in one of the categories and I need their student ID returned.
Since I work mostly in Google Sheets, I was able to get the right formulas but for security reasons I need to move it over to Excel. And I just realized Excel and Sheets formulas are not all from the same world.

Any help would be much appreciated!

attaching a sample

2. ## Re: Converting Unique,Filter,Countif formula in Google Sheets to Excel

C2 and Press Ctrl+Shift+Enter
=IFERROR(SMALL(IF(FREQUENCY(IF(LEFT(\$B\$2:\$B\$11)="D",\$A\$2:\$A\$11),\$A\$2:\$A\$11)=1,\$A\$2:\$A\$11),ROWS(C\$2:C2)),"")

D2 and Press Ctrl+Shift+Enter
=IFERROR(SMALL(IF(FREQUENCY(IF(LEFT(\$B\$2:\$B\$11)="D",\$A\$2:\$A\$11),\$A\$2:\$A\$11)>1,\$A\$2:\$A\$11),ROWS(C\$2:C2)),"")

E2 and Press Ctrl+Shift+Enter
=IFERROR(SMALL(IF(FREQUENCY(IF(LEFT(\$B\$2:\$B\$11)="F",\$A\$2:\$A\$11),\$A\$2:\$A\$11)>0,\$A\$2:\$A\$11),ROWS(C\$2:C2)),"")

Drag C2:E2 Down

3. ## Re: Converting Unique,Filter,Countif formula in Google Sheets to Excel

Thanks, those formulas do seem to work perfectly. Question though: when I tried to adjust the formula to include more rows, it stopped working and just gave me a blank (even after I used ctrl+shift+enter). I'm going to use this spreadsheet as a template for a few teachers to enter data of unknown varying sizes. I want the formula to work say to row 1000 to cover all possibilities. How can I do that?

For instance in C2 I tried:
=IFERROR(SMALL(IF(FREQUENCY(IF(LEFT(\$B\$2:\$B\$100)="D",\$A\$2:\$A\$100),\$A\$2:\$A\$100)=1,\$A\$2:\$A\$100),ROWS(C\$2:C2)),"")

Which just changed all row 11's to row 100, but that didn't seem to work.

4. ## Re: Converting Unique,Filter,Countif formula in Google Sheets to Excel

Please try this and press Ctrl+Shift+Enter

=IFERROR(SMALL(IFERROR(IF(FREQUENCY(IF(LEFT(\$B\$2:\$B\$100)="D",\$A\$2:\$A\$100),\$A\$2:\$A\$100)=1,\$A\$2:\$A\$100),""),ROWS(C\$2:C2)),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)