Thread: SUM VLOOKUPs

Hey Djones;

I'm not quite sure what the values in C2:C6 are in the Dashboard...



However, onto your question, it's not quite a vlookup you're looking for, but an arrayed Sum with two imbedded if statements.

Here's the example equation itself for Cell D11

=SUM(IF(Data!B1:CO1<=Dashboard!B2,IF(Dashboard!B4<=Data!B1:CO1,Data!B5:CO5,0),0))

After entering that, hit CTRL + Shift + Enter to turn it into an array formula (Adds little brackets {} to the end)

The two if statements ensure that they meet both criteria to pull the numbers from the range B5 to CO5 (the unique calls information). If you were to use an and instead of the second if statement, it would treat the entire array as a single instance, and would always return false. Other than that, it functions just like an IF statement, just applied to the entire array rather than one individual cell by using CSE.

If you have anymore questions on how to apply this style of equation to the other ranges or other series in your spreadsheet, don't hesitate to ask.

Ahh, noticed that you're on a Mac, so since you don't have control, shift, enter, the command on the mac is Command + Return

Originally Posted by Miraun

Hey Djones;

I'm not quite sure what the values in C2:C6 are in the Dashboard...



However, onto your question, it's not quite a vlookup you're looking for, but an arrayed Sum with two imbedded if statements.

Here's the example equation itself for Cell D11

=SUM(IF(Data!B1:CO1<=Dashboard!B2,IF(Dashboard!B4<=Data!B1:CO1,Data!B5:CO5,0),0))

After entering that, hit CTRL + Shift + Enter to turn it into an array formula (Adds little brackets {} to the end)

The two if statements ensure that they meet both criteria to pull the numbers from the range B5 to CO5 (the unique calls information). If you were to use an and instead of the second if statement, it would treat the entire array as a single instance, and would always return false. Other than that, it functions just like an IF statement, just applied to the entire array rather than one individual cell by using CSE.

If you have anymore questions on how to apply this style of equation to the other ranges or other series in your spreadsheet, don't hesitate to ask.

the cells C2:C6 are just column numbers I assigned the dates in the "data" sheet, but if we don't need them, that's even better!

I tried using the equation you gave me, I'm getting a sum of "0" however. maybe a little more guidance and/or explanation would be awesome, I can see what we're trying to do here though! Obviously I'll keep hashing away at it and try to get something to work using this method!

Almost there, I promise!

Here's the spreadsheet with the formula in there.

If you're getting just the 0 in there, it needs to be switched over to an array formula. If you select cell D11 on the attached spreadsheet, you'll notice that the equation is bracketed with {}'s. In order to do that, you need to select the equation up top (selecting it will remove the brackets), and then hit Control, Shift, Enter on your keyboard (or Command+Return on a mac) to convert a normal equation into an array equation.

Here's a little blurb to Microsoft's explanation of an array formula:

http://support.microsoft.com/kb/275165

Hello,

I have created the file using VBA and it works perfect. There are three buttons namely 'WTD', 'MTD' & 'YTD'. If you click those buttons, your table will automatically be populated.Try it and let me know the comments. There are some changes that you need to make though. For instance, in the Dashboard table, you have name as Response headers but there are no such things in the Data sheet. Please make sure you give the names matching. As long as it is there the code works perfect. I also tested it. Also rename the Factory name correctly. You have name "Pleasanton" in dashboard but it is named "Cake Shoppe#1" in data sheet. Please make sure of this thing alone.

Thanks and Best Regards,
Abi