# Sumproduct

#### blop38

##### New Member
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### barry houdini

##### MrExcel MVP
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

##### Active Member
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,

#### blop38

##### New Member
Hi,

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.

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

Replies
0
Views
1K
Replies
1
Views
1K
Replies
14
Views
976
Replies
8
Views
838
Replies
19
Views
897

1,195,855
Messages
6,011,974
Members
441,660
Latest member
Neela_Kattappa

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

### Which adblocker are you using?

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

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