"CountIF" or "SumIF" with an extra "S" makes the difference...

Most of the users are very used to formula like "COUNTIF"or "SUMIF"... Excel offers "S" factor adding spice to these handy formulas... 


Sumif: usual syntax is =Sumif(criteria range, criteria, sum range) where you want to sum a given range of cells based on a condition in another range of cells. "Sumifs" acts one step further.


The syntax is =Sumifs(Sum range, Criteria range1, criteria1, Criteria range2, criteria2...) and go on to add upto 9 criteria. This is handy when we need to sum a range based on more than one criteria.




Similarly, we are quite used to "Countif" formula while counting a given range based on a filter criteria. Just like sumifs, countifs offers the same operation with multiple criteria filtration. Countif syntax: =Countif(Count range, Criteria). The countifs syntax gets changed to =countifs(Criteria range1, criteria1, criteria range2, criteria2....). This too works upto 9 criteria.


Excel really makes life simpler.... Isn't it??

Comments

  1. AnonymousMarch 18, 2011 at 3:04 PM

    Thanks for your Tips.
    Pls give the tips with example.

    ReplyDelete
  2. daceyuferMarch 5, 2022 at 5:30 AM

    Betway Casino Review & Bonus Code - JTHub
    Find out everything you need to know about the Betway 시흥 출장마사지 brand in our review. ✓ The software 광명 출장샵 and 안동 출장샵 all 대전광역 출장샵 the important details of this 성남 출장마사지 operator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Keyboard Shortcut to add/delete rows or columns in Excel

Image
" Ctrl + + " (add rows or columns) and " Ctrl + - " (delete rows or columns) are the easy shortcuts one may remember while adding and deleting rows/columns in Microsoft Excel. Adding rows or columns: If you wish to add rows above the current cell... just press "shift + spacebar" (discussed in my earlier post) to select the current row and then press "Ctrl + +". It will insert a row over the current row. Press "Ctrl + +" repeatedly to insert as many rows. Similarly, "ctrl + spacebar" and then "Ctrl + +" adds a column before the current column. You may try pressing "Ctrl + +" without selecting a row or a column. In such a case, you may get a pop-up (as shown in the image) where you can choose an option of adding a row or a column. Deleting rows or columns: "shift + spacebar" followed by "Ctrl + -" deletes the selected row & "ctrl + spacebar" followed by "Ct
Read more

Shortcut to Copy the contents of the cell above or on the left

Image
Most of us may have not tried this. We are very much familiar with the world-famous "Ctrl + C" and "Ctrl + V" but have you tried " Ctrl + D " and " Ctrl + R " in excel?? " Ctrl + D " offers the flexibility of copying the content of the cell above to the next cell or a range of cell below. Here you just need to use one action instead of the regular copy and paste. For example:  If you have A1 with some content and you wish to copy the same content to A2, just go to A2 and press "Ctrl + D". The content of A1 gets copied to A2 in one just one command. If you wish to copy the contents of A1 to a range of cells below A1 (say A2 to A10), select A1 to A10 (as shown in the image on the right) and press "Ctrl + D"... then see the magic!! " Ctrl + R " also offers a similar functionality. Using this you can copy the contents to the right cells i.e. if you wish to copy the contents of A1 to B1. The proc
Read more

Alternative for SUMIF and SUMIFS

Image
Ever wondered while using "SUMIF" function, the sum range cannot be more than one column!!  This indeed is a big drawback while using a large data set where you need to sum matching certain conditions. I often end up using multiple sumif with "+" which makes the formula very long. So was thinking of some alternative that can make life easier. Thanks to the search engines these days, I did come across a workaround. Refer to the above image: In the above example, I have a table with 4 columns where H1 and H2 are identifiers. H3 and H4 is the data which needs to be summed up. Condition 1 (alternative for SUMIF): I need to sum values of H3 and H4 where the identifier H2 is "Y". The formula will be =SUM(IF(B2:B5=B9,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this:  {=SUM(IF(B2:B5=B9,C2:D5))} Condition 2  (alternative for SUMIFS) : I need to sum values of H3 and H4 where the
Read more