Friday, July 29, 2016

Alternative for SUMIF and SUMIFS

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: 

Condition 2 (alternative for SUMIFS):
I need to sum values of H3 and H4 where the identifier H1 is "N" and H2 is "Z".
The formula will be =SUM(IF(A2:A5&B2:B5=B10,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this: 

Try this out in a larger data set and check your results.

Disclaimer: This will work only in a array i.e. without "Ctrl + Shift + Enter" , the result may be "#N/A"



  2. Your post is very informative one. i will bookmark this blog for my further reads. Thank you for being posting us. Custom essay writing service can help with term paper writing as we recognize that troubles survive for lots of students through a variety of circumstances, which put off them since term paper writing.

  3. Hey keep posting such good and meaningful articles.

  4. Nice post, things explained in details. Thank You.

  5. This is the precise weblog for anybody UN agency must search out out concerning this subject. You notice such a lot its nearly arduous to argue with you. You completely place a spanking new spin on a topic that is been written concerning for years. Nice stuff, merely nice!

  6. Hi, extremely nice effort. everybody should scan this text. Thanks for sharing.

  7. You left me wowed, I feel luck I found your website. Keep producing the great contentCustom Research Paper Writing Services

  8. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. business search engine optimisation