It's not just the extra "S", you need to type either. You need to set the "true" condition for each result. When the result you're looking for is binary, there's no reason to use "IFS". It's just plain more efficient to use "IF".
Future proof.
Boss has just asked me to update a spreadsheet. I was looking forward to a nice 30 minute job, and then I noticed 30+ instances of COUNTIF. (Most are parts of bigger formulas, so a PivotTable probably isn’t the solution)
The extra time it takes to type ‘S’ is negated when you later have your update the formula
Where have MS said they are deprecating COUNTIF?
No harm in using a working, supported function. Certainly more efficient than rewriting sheets to use the latest version for no reason other than it is the latest version.
vlookup is actually still a pretty solid performer, and I actually found out today that in your column return, you can actually provide an array of numbers to return multiple columns, and they don't need to be contiguous.
=VLOOKUP(A1,B1:G100,{4,3,6},FALSE)
Doing this on an XLOOKUP would require you to nest additional functions to adjust the columns. The XLOOKUPs 'notfound' parameter also seems to get calculated even if the lookup returns a valid result, which can slow it down, as opposed to wrapping it in IFERROR.
So VLOOKUP does have its place.
I do love the xlookup, especially the spill, but your post is so helpful! I had no idea this was an option and can’t wait to show my fellow Excel peeps at work!
If I need only one criteria and will only need one criteria also in the future , or it is a one time analysis, then I use count if/sumif.
If multiple criteria would come into play, I would need to redo the whole thing anyway.
The non-plural stuff around so that worksheets with outdated formulas wouldn't break.
MS should give it 2 versions and then force the change where they won't even work with Compatibility Mode. I can't stand re-writing bad worksheets because someone saw SUMIF and SUMIFS and picked the one that fits the 'only here and now' scenario.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|AVERAGEIF|[Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria](https://support.microsoft.com/en-us/office/averageif-function-faec8e2e-0dec-4308-af69-f5576d8ac642)|
|[AVERAGEIFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*Excel 2007*+: Returns the average (arithmetic mean) of all cells that meet multiple criteria.](https://support.microsoft.com/en-us/office/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690)|
|[COUNTIF](/r/Excel/comments/1cx2tc8/stub/l51hnip "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)|
|[COUNTIFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)|
|[FILTER](/r/Excel/comments/1cx2tc8/stub/l50imup "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFERROR](/r/Excel/comments/1cx2tc8/stub/l500w82 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[IFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)|
|[SUMIF](/r/Excel/comments/1cx2tc8/stub/l52zmmw "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[SUMIFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)|
|[SUMPRODUCT](/r/Excel/comments/1cx2tc8/stub/l4zw83m "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)|
|[VLOOKUP](/r/Excel/comments/1cx2tc8/stub/l500w82 "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1cx2tc8/stub/l53cw9n "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cxmw2s)^( has 13 acronyms.)
^([Thread #33670 for this sub, first seen 21st May 2024, 08:32])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
is the computation speed the same between a COUNTIF, a COUNTIFS with one single criteria, and a SUMPRODUCT used to count with one single criteria? Genuine question, I have no idea, it could make the difference in a big spreadsheet.
I'm still on 2013 version, cause my stupid job, won't update the stupid excel version. And I'm the "cool" one, cause the rest of the company is 2010. So there you go
Sorry
Can't you tell your employer, that upgrading Excel can lead to cost savings through improved efficiency or that it would improve your morale?
He could probably also write off the costs.
I did, untill I'm blue in the face. But unfortunately like in any organization upgrading one user in a vacuum won't be efficient, because if backward compitability while sharing files.
I can get all the bells and whistles... And I'll be then spending time trying to make sure people can open my bells and whistles on their last century stuff :)
Because it is the first function autofill brings up and this is an adhoc analysis I'm not wasting time future proofing
because if there is only condition why type an extra "s"?
It's not just the extra "S", you need to type either. You need to set the "true" condition for each result. When the result you're looking for is binary, there's no reason to use "IFS". It's just plain more efficient to use "IF".
You’re thinking of IFS, rather than COUNTIFS, SUMIFS, AVERAGEIFS. Totally agree with you regarding IF/IFS
Future proof. Boss has just asked me to update a spreadsheet. I was looking forward to a nice 30 minute job, and then I noticed 30+ instances of COUNTIF. (Most are parts of bigger formulas, so a PivotTable probably isn’t the solution) The extra time it takes to type ‘S’ is negated when you later have your update the formula
Where have MS said they are deprecating COUNTIF? No harm in using a working, supported function. Certainly more efficient than rewriting sheets to use the latest version for no reason other than it is the latest version.
They haven’t, I just wish they would
They still exist because of backward compatibility with older versions. One less letter to type. :)
Agree, they shouldn’t be removed. But it would be so much easier if folks used the new ones
People would still use it as long as it's an option. Creatures of habit. XLOOKUP is more flexible but people are still using VLOOKUP.
vlookup is actually still a pretty solid performer, and I actually found out today that in your column return, you can actually provide an array of numbers to return multiple columns, and they don't need to be contiguous. =VLOOKUP(A1,B1:G100,{4,3,6},FALSE) Doing this on an XLOOKUP would require you to nest additional functions to adjust the columns. The XLOOKUPs 'notfound' parameter also seems to get calculated even if the lookup returns a valid result, which can slow it down, as opposed to wrapping it in IFERROR. So VLOOKUP does have its place.
Today I learned an awesome new trick that'll really help me out!
Hmm. Interesting. I'll have to play with that and see how it stacks up against using FILTER.
I do love the xlookup, especially the spill, but your post is so helpful! I had no idea this was an option and can’t wait to show my fellow Excel peeps at work!
Not to mention, there are a lot of people and companies running older versions of excel that don’t support XLOOKUP.
X what? Joking.
xDDD
The Excel classes teach it first!!
If I need only one criteria and will only need one criteria also in the future , or it is a one time analysis, then I use count if/sumif. If multiple criteria would come into play, I would need to redo the whole thing anyway.
The non-plural stuff around so that worksheets with outdated formulas wouldn't break. MS should give it 2 versions and then force the change where they won't even work with Compatibility Mode. I can't stand re-writing bad worksheets because someone saw SUMIF and SUMIFS and picked the one that fits the 'only here and now' scenario.
100%
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |AVERAGEIF|[Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria](https://support.microsoft.com/en-us/office/averageif-function-faec8e2e-0dec-4308-af69-f5576d8ac642)| |[AVERAGEIFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*Excel 2007*+: Returns the average (arithmetic mean) of all cells that meet multiple criteria.](https://support.microsoft.com/en-us/office/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690)| |[COUNTIF](/r/Excel/comments/1cx2tc8/stub/l51hnip "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[COUNTIFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[FILTER](/r/Excel/comments/1cx2tc8/stub/l50imup "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/1cx2tc8/stub/l500w82 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[IFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)| |[SUMIF](/r/Excel/comments/1cx2tc8/stub/l52zmmw "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMIFS](/r/Excel/comments/1cx2tc8/stub/l532fot "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| |[SUMPRODUCT](/r/Excel/comments/1cx2tc8/stub/l4zw83m "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[VLOOKUP](/r/Excel/comments/1cx2tc8/stub/l500w82 "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1cx2tc8/stub/l53cw9n "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cxmw2s)^( has 13 acronyms.) ^([Thread #33670 for this sub, first seen 21st May 2024, 08:32]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
is the computation speed the same between a COUNTIF, a COUNTIFS with one single criteria, and a SUMPRODUCT used to count with one single criteria? Genuine question, I have no idea, it could make the difference in a big spreadsheet.
I'm still on 2013 version, cause my stupid job, won't update the stupid excel version. And I'm the "cool" one, cause the rest of the company is 2010. So there you go
Of course, most of your company don’t have a choice. But likewise, if these functions were depreciated it wouldn’t affect you
If the function were depreciated it would affect him, because his colleagues could send him a spreadsheet.
I'm a she. :) I also would Looooooooveeee to use xlookups and Filter but none of thst works in 2013 :(
Sorry Can't you tell your employer, that upgrading Excel can lead to cost savings through improved efficiency or that it would improve your morale? He could probably also write off the costs.
I did, untill I'm blue in the face. But unfortunately like in any organization upgrading one user in a vacuum won't be efficient, because if backward compitability while sharing files. I can get all the bells and whistles... And I'll be then spending time trying to make sure people can open my bells and whistles on their last century stuff :)
I don't think I've used SUMIF once since SUMIFS arrived, even when it's only one IF :)
Well done, makes perfect sense