### 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.

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))}

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:

{=SUM(IF(A2:A5&B2:B5=B10,C2:D5))}

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"

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.

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 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:

{=SUM(IF(A2:A5&B2:B5=B10,C2:D5))}

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"

Hey keep posting such good and meaningful articles.

ReplyDeleteNice post, things explained in details. Thank You.

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

ReplyDeleteOn the Secrets of a Sugar Daddy podcast there are True life podcasts my guests and I will pull back the curtain and give you first hand accounts of our experiences. We will cover a wide range of topics from building the right kind of profile , to avoiding the wrong kind of people

ReplyDelete