Sumproduct

blop38

New Member
Joined
Apr 8, 2013
Messages
2
Hi all,

I found a wonderful answer from Aladin Akyrek on this thread which solved my problem. However I do not understand it works, and as the thread was 4 years ago I open this new thread to post my question:

How does SUMPRODUCT work in the following formula
Code:
SUMPRODUCT(SUMIF(range_to_check_in_data, range_data, range_to_sum))

( it applies to COUNTIF too — SUMPRODUCT(COUNTIF(range_to_check_in_data, range_data)) )

knowing the prototype of this function:
Syntax

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ... are 2 to 255 arrays whose components you want to multiply and then add.
and that it multiplies all the components of the arrays and then return the sum of these products.

In our case, we have only one argument, so what kind of magic is happening here?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hello blop38, welcome to MrExcel

If you have a SUMIF formula with an array or range as the criterion then the result is an array (which is the same size as the criterion range).....so SUMPRODUCT is simply used here to sum that array to avoid CTRL+SHIFT+ENTER, so really you only need SUM like

=SUM(SUMIF(range_to_check_in_data,range_data,range_to_sum))

......but that has to be "array-entered"

The COUNTIF version works in the same way
 
Upvote 0
This is my take on it..

Data to check = A2:A100
Value to check data against = A1
Data to Sum = B2:B200


=Sumproduct((A2:A100=A1)*B2:B100)

You may need to use a uniary operator against the initial range, depending on the type of data you're approaching. In that case it would be =Sumproduct(--(A2:A100=A1)*B2:B100)

Best thing to do is set this up with a small data array and evaluate it, you'll see that the array builds a series of true/false entries for those cells within the data to check range based on whether they meet the criteria. These are converted to 1/0 values to allow correct multiplication/addition of the data to sum range values.

HTH,

slinky
 
Upvote 0
Hi,
Sorry for the delayed answer (yesterday a bug prevented me to access this page) and thanks a lot for your quick answers!

barry,
ok, I got your explanation about what is happening, but I am wondering on the last part about SUM and SUMPRODUCT. In this case, they’d be equivalent, but you say using SUMPRODUCT avoid the need for ctrl+shift+enter. Why so? Is this special to SUMPRODUCT only?

Actually I discovered the array formulae the day I posted my question, and I have very rarely used Excel actually, so please excuse my ignorance.

sklinky,
Yes I guessed this possibility when I saw examples of the array formulae, but what makes my case different is that I needed to check the data not against a value, but against a range of values!

In fact, it seems it works as well for ranges too, but I hadn’t even suspected that. I started looking in a combination of MATCH (or *LOOKUP) and COUNTIF (and SUMIF too, but that’s the same logic).
And I blocked on the fact that I couldn’t find any way to reference the cell being currently counted by COUNTIF (or summed by SUMIF). Something like:
COUNTIF(range, MATCH(ref_of_the_current_cell, range_to_check_against, 0) ).
Do you see what I mean by “ref_of_the_current_cell”?
Algorithmically it’d just be:
Code:
for all cell C in range {
     if( C in range_to_check_against) then COUNT( C )
}

I felt like there would be a lot of situations in which I would run into this limitation, and I had the feeling that the only way to go around would be to manually create a new column that checks the criteria (here whether or not is in range_to_check_against), and then apply the looping (COUNTIF/SUMIF) formula on the later.

Sorry if I’m not clear, don’t hesitate to ask me to clarify.

Is there a way to give the reference of the cell being “looped on” in formulae such as COUNTIF/SUMIF? Or can array-formulae provide a valuable alternative for each case?


Thanks a lot,
blop
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top