• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT Question

P

PaulFogel123

Member
Hello,

Attached is a simple file with a simple SUMPRODUCT formula. The top table is the data; the bottom table calculates the average weight with this formula:
=SUMPRODUCT(Weight,D4:D20)

The dates will be updated every month. I want to replace the D4:D20 part (in this case, that's September) with a formula that will find the correct columns from the data table based on the dates in the bottom table. Any solutions?

Paul
 

Attachments

  • Test File 3.xlsx
    73.2 KB · Views: 6
Hi ,

If the first parameter of the OFFSET function is a range , instead of a single cell , then the COUNTA function can be eliminated. The following formula will give the same result :

=SUMPRODUCT(Weight,OFFSET($C$4:K$20,,MATCH(D$25,$C$3:$K$3,0)-1,,1))

Even simpler would be :

=SUMPRODUCT(Weight,INDEX($C$4:K$20,,MATCH(D$25,$C$3:$K$3,0)))

Narayan
 
Thank you both. I never could have done this myself. Can you explain how this formula works so I can use it in other applications?
Paul
 
Thank you, that works well also. How do I distinguish what formulas should be entered as an array and which not? On those I've been unsure about, entering as an array formula doesn't affect the result, but on those formulas that are definitely array, such as yours, it must be entered as an array or the formula doesn't work.
 
Hi Paul ,

First , every formula can be entered using CTRL SHIFT ENTER ; if the formula is not an array formula , it makes no difference , and the resulting value will be correct. If the formula is an array formula , it makes a difference , and the resulting value will be correct.

If you do not want to take this approach , there are two ways to detect when an array formula is in place , and therefore entering it requires the use of CTRL SHIFT ENTER.

I will take a contrived example to illustrate the procedure.

Suppose we have a list of numbers , in the range E5:E11 ; I will name this range List. Let us assume that the following numbers have been entered in this range :

2 , 3 , 5 , 3 , 7 , 6 , 2

Now , suppose we want to count the number of even numbers in this range ; there are obviously simpler formulae which will do this , but since I want to illustrate the point about array formulae , I will take the following formula :

=SUM(IF(MOD(List,2) = 0,1))

Enter the above formula in any cell ; you should see the displayed value as the error value #VALUE! ; an error value can be either because the formula is returning an error value , or because Excel is unable to display the correct value ; in this case , it the latter.

To know this , place the cursor in the cell containing the formula , and press F2 F9 ;you should see the number 3 , signifying that there are 3 even numbers in the above range.

Now , press F2 to enter edit mode , and then enter the formula by pressing CTRL SHIFT ENTER ; the cell will now display the correct value of 3.

Thus , using F2 and F9 , we can examine the real value , and it may be possible , though this is not true in every case , that entering the formula using CTRL SHIFT ENTER will return the correct value.

Now , for the second method.

With the cursor in the cell containing the formula , click on the Evaluate Formula button in the Ribbon.

As you step through the process by clicking the Evaluate button repeatedly , you will see that nowhere are the values in the range being used to arrive at the result.

After exiting the process , enter the formula using CTRL SHIFT ENTER , and now again go through the Evaluate Formula process ; this time , you should see all the values in the range being used to arrive at the correct result.

We need to be aware that an array formula which has not been entered using CTRL SHIFT ENTER , need not always display the #VALUE! error value thus alerting us to this possibility.

For example , if I slightly modify the above formula , as follows :

=SUM(IF(MOD(N(List),2) = 0,1))

and enter the formula without using CTRL SHIFT ENTER , the cell containing the formula will display 1 ; however , do not be fooled into thinking that this is the correct result.

If you go through the Evaluate Formula process , you will see that the formula evaluation does not use the numbers in the range ; instead , it is using only the first number in the range.

Now , enter the formula using CTRL SHIFT ENTER , and go through the formula evaluation process once more ; this time , you should see all the numbers being used to arrive at what will be the correct result.

Narayan
 
You must log in or register to reply here.
Back
Top