(New user) Lookup/auto-generate question

[jlcrunkleton]

Hello,
I'm new to this site and hoping that someone here can help me out. Here's a screenshot of my situation...

I would like to auto-generate column I based on criteria in columns A and C. The values to be auto-generated with are contained in column O. For example, A2 is equal to 5/7/07 and C2 is equal to 3...I2 should be auto-generated with the value 132 due to the matching of HOUR and DATE in columns L and M. L9 is equal to 3 and M9 is equal to 5/7/07...the corresponding value in O8 is equal to 132, which is the value to be generated in I2.

Any help is much appreciated...hopefully I was able to clearly depict my issue!

Thanks in advance.
~Josh

 

[Lewiy]

Welcome to the board!

Try something like this in I1 and copy down:

=SUMPRODUCT(--($L$2:$L$100=C1),--($M$2:$M$100=A1),$O$1:O99)

 

[jlcrunkleton]

Hi,
Thanks a lot for your reply! Unfortunately, I receive #DIV/0! errors when I try to use the formula you suggested. Here's an updated screenshot:

I've never used the SUMPRODUCT formula before, so I was trying to decipher the formula as I entered it into Excel. The only things I altered from the formula you provided were the upper limits for my table in columns L, M and O (they go down to row 241).

Is this formula pulling the #DIV/0! error from the highlighted value that corresponds to HOUR = 0? There is no data for that value, so the first value that should be returned for the auto-generation is the value of 132 that corresponds to HOUR = 3.

Thanks again and I look forward to any responses!
~Josh

 

[Lewiy]

First off, you need to change the reference to column O in the formula, you will notice that in my example the range for O is offset by 1 row because the totals you are trying to get are in a different row to the values you are testing against, so try changing the O reference to:

$O$1:$O$40

 

[jlcrunkleton]

Thanks for your reply. I changed the formula to reflect the offset in column O and still receive a #DIV/0! error when I input the formula in column I.

As of now, this is the formula that I am inputting:

=SUMPRODUCT(--($L$2:$L$241=C2),--($M$2:$M$241=A2),$O$1:$O$240)

Any other suggestions or things I may be doing incorrectly?

I'm attempting to better understand the SUMPRODUCT function...what exactly does the "--" accomplish when placed before the parentheses in the first 2 array arguments?

Thanks again!
~Josh

 

[jlcrunkleton]

Lewiy - nevermind! I think I figured out what the issue is. I replaced the #DIV/0! errors in the highlighted cells with zeros and it looks like the formula was able to carry through with its execution. Thanks again for all of your help!!
~Josh

 

[Lewiy]

I think the problem may be that some of the values in column O are DIV/0 errors. I must admit I did not test with these errors in place. Try taking these errors out or replacing them with numbers.

With regard to how SUMPRODUCT works, the first array argument checks down column L and returns a single dimensional array of TRUE and FALSE’s depending on which cells in the array are equal to C2. So for example, if the second and fourth items in the array are equal to C2 then it will return FALSE,TRUE,FALSE,TRUE. By putting the double minus symbol in front, it coerces the TRUE’s and FALSE’s into their numerical equivalents so you get 0,1,0,1.

Then the second array argument does the same comparing column M to A2 so let’s say that the first and fourth items in column M were equal to A2 then the array argument would return 1,0,0,1. SUMPRODUCT then multiplies the 2 arrays together:
0x1=0
1x0=0
0x0=0
1x1=1

So now you have a new array: 0,0,0,1. The final array argument for column O has no requirements to be equal to anything, etc, so it fills the array with the values in column O. Let’s say that in column O you have the values 10,12,14,16. SUMPRODUCT multiplies this array to the one we got earlier:
0x10=0
0x12=0
0x14=0
1x16=16
And then adds it all together giving the result 16. What it has basically done is added together all of the values in column O where its equivalent values in columns L and M meet the criteria you have set.

Hope that all makes sense!