SUM(SUMIFS( Array CTRL+SHIFT+ENTER Vs. ENTER

[CyrusTheVirus]

Can anyone please assist me with clearly explaining why I must use CTRL+SHIFT+ENTER for formulas 1 and 2, but not for formula 3?

H1=Jerry
H2=Bob
H3=Tony

Range 'Names' = H1:H3

Formula 1:

{=SUM(SUMIFS(B2:B10,A2:A10,H1:H3))}

Formula 2:

[/CODE]{=SUM(SUMIFS(B2:B10,A2:A10,Names))}[/CODE]

Formula 3:

[/CODE]=SUM(SUMIFS(B2:B10,A2:A10,{"Jerry","Bob","Tony"))[/CODE]

 

[CyrusTheVirus]

Edited the Formulas to contain code parameters...

Can anyone please assist me with clearly explaining why I must use CTRL+SHIFT+ENTER for formulas 1 and 2, but not for formula 3?

H1=Jerry
H2=Bob
H3=Tony

Range 'Names' = H1:H3

Formula 1:

{=SUM(SUMIFS(B2:B10,A2:A10,H1:H3))}

Formula 2:

{=SUM(SUMIFS(B2:B10,A2:A10,Names))}

Formula 3:

=SUM(SUMIFS(B2:B10,A2:A10,{"Jerry","Bob","Tony"))

 

[Jonmo1]

When the criteria is entered as a range (Names or H1:H3), it's expected to be a single cell reference.
So it's really only considering one of the 3 cells in H1:H3 (which one is relative to the Row# you entered the formula in)
Entering it with CSE makes it process the range as an array.

Entering the criteria as a Constant Array {"a","b","c"}, that makes sumifs process it as an array.


You could change the SUM to SUMPRODUCT in the 1st 2 formulas to avoid the CSE requirement.
It will still be an array processing formula, but just don't need to enter with CSE.

 

[CyrusTheVirus]

When the criteria is entered as a range (Names or H1:H3), it's expected to be a single cell reference.
So it's really only considering one of the 3 cells in H1:H3 (which one is relative to the Row# you entered the formula in)
Entering it with CSE makes it process the range as an array.

Entering the criteria as a Constant Array {"a","b","c"}, that makes sumifs process it as an array.


You could change the SUM to SUMPRODUCT in the 1st 2 formulas to avoid the CSE requirement.
It will still be an array processing formula, but just don't need to enter with CSE.

Thank you for the reply Jonmo1. It is beginning to click. Though, I do have a follow up question. Really, it is just a general question of how SUMIFS 'really' works.

What exactly is going on with SUMIFS with the following formula?

SUMIFS(B2:B4,A2:A4,"Bob")

The excel evaluate formula feature doesn't show anything more than the code, except for the answer. The evaluate feature doesn't show any more steps like it does for the SUMPRODUCT function...

I'm just curious to know 'exactly' what's going on with the SUMIFS function. For instance, when I try the SUMPRODUCT in the evaluate formula:

=SUMPRODUCT(--(A2:A4="Bob"),(B2:B4))

Then I see more to what's going on after hitting evaluate:

=SUMPRODUCT(--({FALSE;TRUE;FALSE}),(B2:B4)

Then after hitting evaluate again I see further calculations by excel:

=SUMPRODUCT(--{FALSE;TRUE;FALSE},(B2:B4)

Then

=SUMPRODUCT({0;1;0},{0;10;20})

Then the answer:

10

But, I'm just curious what calculations are made by excel for the SUMIFS function... Could you please help explain what excel is 'really doing' when it is calculating that simple above SUMIFS formula? Perhaps an example like the above SUMPRODUCT breakdown would help me understand it?

 

[Jonmo1]

Sorry, I can't give any real detailed answer.
All i know is the general answer.
=SUMIFS(B2:B4,A2:A4,"Bob")

For every cell in A2:A4, if it equals "Bob", it sums the corresponding cell from B2:B4.


Now before you ask...lol

Throw in the multi criteria
=SUM(SUMIFS(B2:B4,A2:A4,{"Fred","George","Bob"}))

This basically creates 3 sumifs formula, 1 for Fred, 1 for George and 1 for Bub
Then the SUM adds the results of each together.
It would be the same as if you wrote
=SUMIFS(B2:B4,A2:A4,"Fred")+SUMIFS(B2:B4,A2:A4,"George")+SUMIFS(B2:B4,A2:A4,"Bob")


Hope that helps.

 

[Aladin Akyurek]

SUMIFS(B2:B10,A2:A10,H1:H3)

calculates three conditional sums, i.e., for H1, H2, and H3 which is an array of 3 conditional values. Since these 3 values cannot 'fit' a single cell, we just see the first conditional value (or just one of them or none).

Since we are interested in the sum of these conditional sums, we wrap this SUMIFS expression into a totaling function:

1.

SUM(SUMIFS(B2:B10,A2:A10,H1:H3))

SUM will also see just one of the conditional values (or none giving 0). Applying control+shift+enter forces SUM to consider all of the available values and yields the desired value (That is, SUM becomes an array-processing function when combined with control+shift+enter).

2.

SUMPRODUCT(SUMIFS(B2:B10,A2:A10,H1:H3))

does return the total of all the conditional values SUMIFS calculates. Here we don't need control+shift+enter because SUMPRODUCT is designed to operate as an array-processing function whenever it's called.

Note that PRODUCT of SUMPRODUCT creates an array values, SUM of SUMPRODUCT sums those values. (We don't need SUMSUMIFS though!)

Note also that naming H1:H3 does not change anything regarding the foregoing story.

3.

SUM(SUMIFS(B2:B10,A2:A10,{"Jerry","Bob","Tony"))

succeeds without control+shift+enter because SUM is handed an array constant, not an array that must be converted into an array constant. This holds for SUMPRODUCT too.

Hope this helps.

 

[CyrusTheVirus]

Sorry, I can't give any real detailed answer.
All i know is the general answer.
=SUMIFS(B2:B4,A2:A4,"Bob")

For every cell in A2:A4, if it equals "Bob", it sums the corresponding cell from B2:B4.


Now before you ask...lol

Throw in the multi criteria
=SUM(SUMIFS(B2:B4,A2:A4,{"Fred","George","Bob"}))

This basically creates 3 sumifs formula, 1 for Fred, 1 for George and 1 for Bub
Then the SUM adds the results of each together.
It would be the same as if you wrote
=SUMIFS(B2:B4,A2:A4,"Fred")+SUMIFS(B2:B4,A2:A4,"George")+SUMIFS(B2:B4,A2:A4,"Bob")


Hope that helps.

Hi Jonmo1, thanks very much. That does help.

I'm new to arrays and they always make my head spin. I've been re-reading your couple replies over and over to help it 'sink in'. I think it's starting to.

And I really like the way you worded this part:

For every cell in A2:A4, if it equals "Bob", it sums the corresponding cell from B2:B4.

It helps clarify what's going on. Thanks again!

 

[CyrusTheVirus]

Hi Aladin,

Thanks for your reply! That does help clarify a bit. I've been re-reading and thinking about both your and Jonmo1's replies all morning trying to 'grasp' it all. It might take a bit longer. Seems that understanding the specific terminology is important.

I have been working in excel for 2 1/2 years, and have really started studying it for the past year. I've heard your name mentioned multiple times, not only in forums, but from Mike 'ExceIsFun' Girvin as well. He gives you high praise! I've read his book 'Slaying Excel Dragons' and most of his book, 'Ctrl+Shift+Enter'. Though, I'll be honest, the Ctrl+Shift+Enter book made my head spin the first time around. However, seeing how powerful arrays are, I think I'm going to give it another shot, and hopefully it'll make more sense the second time around.

Thanks again for your help with this and also for your past posts that have come across that have helped me!!!