Variables from which to choose: Month, Store#, Type (Fruit or Veg), Color

Table looks like this:
Fruit
Vegetable
Month Store# Red Green Orange Red Green Orange




The data is the inventory of red fruits, green vegetables, etc in each store
at the end of each month.

What does my formula look like to lookup that inventory volume for a given
set of variables?

You can use SUMPRODUCT for multi-conditions. For red fruit from store1 in
january something like:
=SUMPRODUCT(--(A2:A100="January"),--(B2:B100="Store1"),--(C2:C100="Fruit"),(D2:D10))

This assumes month is in column A, store in column B, Type in column C, and
inventory is in column D. Try playing around with the different critieria to
get what you need. Note that the arrays need to be same size, and unless
using XL 2007, you can't callout entire column (A:A).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"44judester" wrote:

Variables from which to choose: Month, Store#, Type (Fruit or Veg), Color

Table looks like this:
Fruit
Vegetable
Month Store# Red Green Orange Red Green Orange




The data is the inventory of red fruits, green vegetables, etc in each store
at the end of each month.

What does my formula look like to lookup that inventory volume for a given
set of variables?

Thanks for the reply, Luke. SUMPRODUCT treats non-numeric entries as zero so
I keep getting a zero result.

The way lined up in my example was a bit misleading....this is my real-life
example:

Aug Sep
------ ------
Dept Shift ST OT ST OT
------ ------- ------ ------ ------ -------
443 1st 493 41 986 81
443 2nd 493 41 986 81
443 3rd - - - -
444 1st 141 16 282 32
444 2nd 141 16 282 32
444 3rd - - - -
445 1st 422 48 845 102
445 2nd 352 40 704 85
445 3rd - - - -
448 1st 211 15 422 30
448 2nd 282 20 563 40
448 3rd 70 5 141 10
449 1st 563 27 1,126 55
449 2nd 422 21 845 41
449 3rd 70 3 141 7

this is an exerpt from a large table of straight-time and overtime by month,
dept and shift. I want to be able to pull data from this (just one
intersection point) into other spreadsheets given the month, dept, shift and
whether ST or OT. What is that formula in the other spreadsheets?

"Luke M" wrote:

You can use SUMPRODUCT for multi-conditions. For red fruit from store1 in
january something like:
=SUMPRODUCT(--(A2:A100="January"),--(B2:B100="Store1"),--(C2:C100="Fruit"),(D2:D10))

This assumes month is in column A, store in column B, Type in column C, and
inventory is in column D. Try playing around with the different critieria to
get what you need. Note that the arrays need to be same size, and unless
using XL 2007, you can't callout entire column (A:A).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"44judester" wrote:

Variables from which to choose: Month, Store#, Type (Fruit or Veg), Color

Table looks like this:
Fruit
Vegetable
Month Store# Red Green Orange Red Green Orange




The data is the inventory of red fruits, green vegetables, etc in each store
at the end of each month.

What does my formula look like to lookup that inventory volume for a given
set of variables?

Say your table is in Sheet1!A1:F17, whe

A3:A17 list your Depts
B3:B17 list your Shifts
C1 shows Oct and E1 shows Sep
C2:F2 lists ST or OT, respectively for the months


then in Sheet2:

A2: Dept (e.g 444)
B2: Shiif (e.g. 2nd)
C2: Month (e.g Sep)
D2: ST or OT (e.g. OT)

then formula to extract intersect:


Code:
--------------------
=INDEX(Sheet1!$C$3:$F$17,MATCH(A2&B2,Sheet1!$A$3:$ A$17&Sheet1!$B$3:$B$17,0),MATCH(1,(Sheet1!$C$2:$F$ 2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B1:E1=C2)),0))
--------------------


this formula is an array formula and must be confirmed with
CTRL+SHIFT+ENTER not just ENTER.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122286

Not sure how that last MATCH sequence works, but it does! Thanks!

"NBVC" wrote:


Say your table is in Sheet1!A1:F17, whe

A3:A17 list your Depts
B3:B17 list your Shifts
C1 shows Oct and E1 shows Sep
C2:F2 lists ST or OT, respectively for the months


then in Sheet2:

A2: Dept (e.g 444)
B2: Shiif (e.g. 2nd)
C2: Month (e.g Sep)
D2: ST or OT (e.g. OT)

then formula to extract intersect:


Code:
--------------------
=INDEX(Sheet1!$C$3:$F$17,MATCH(A2&B2,Sheet1!$A$3:$ A$17&Sheet1!$B$3:$B$17,0),MATCH(1,(Sheet1!$C$2:$F$ 2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B1:E1=C2)),0))
--------------------


this formula is an array formula and must be confirmed with
CTRL+SHIFT+ENTER not just ENTER.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122286

44judester;443685 Wrote:
Not sure how that last MATCH sequence works, but it does! Thanks!

"NBVC" wrote:


Say your table is in Sheet1!A1:F17, whe

A3:A17 list your Depts
B3:B17 list your Shifts
C1 shows Oct and E1 shows Sep
C2:F2 lists ST or OT, respectively for the months


then in Sheet2:

A2: Dept (e.g 444)
B2: Shiif (e.g. 2nd)
C2: Month (e.g Sep)
D2: ST or OT (e.g. OT)

then formula to extract intersect:


Code:
--------------------

=INDEX(Sheet1!$C$3:$F$17,MATCH(A2&B2,Sheet1!$A$3:$ A$17&Sheet1!$B$3:$B$17,0),MATCH(1,(Sheet1!$C$2:$F$ 2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B1:E1=C2)),0))
--------------------


this formula is an array formula and must be confirmed with
CTRL+SHIFT+ENTER not just ENTER.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'multi-level indexing? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=122286)



The last match looks at two conditions... first whether the 2nd row is
OT or ST... and the other conditon checks for the Month... since you
only have months on every other cell in the row.. it needs to check if
the cell in the same column as the OT, ST is filled or not.. the + sign
acts as an OR checking if cell in same column or cell in column to the
left has the correct month.

The 1 at the beginning, looks for the first position of a 1 in the
resulting array of this lookup array (which is also a conditional
array): (Sheet1!$C$2:$F$2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B 1:E1=C2))


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122286