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.

ReplyDelete