Array Formula
 

Hello smart folks.

I have a spread sheet that looks like ths:

Item Number Jul-03 Aug-03 Sep-03 Oct-03
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

I need to fil the information as follows:

7/03 ABC Qty
8/03 ABC Qty
7/03 CDE Qty
8/03 CDE Qty

etc. Can someone please help?

Array Formula
 

Jennifer

You don't need an array formula, try this:-

=SUMPRODUCT(($A$2:$A$6=A10)*($B$1:$E$1=B10)*($B$2: $E$6))

with a10 = abc
b10 = the date you are looking for

"Jennifer" wrote:

Hello smart folks.

I have a spread sheet that looks like ths:

Item Number Jul-03 Aug-03 Sep-03 Oct-03
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

I need to fil the information as follows:

7/03 ABC Qty
8/03 ABC Qty
7/03 CDE Qty
8/03 CDE Qty

etc. Can someone please help?

Array Formula
 

I'm sorry Mike, I don't think I was clear. I meant by "qty" to return the qty
in that cell. In other words, I need the function to return the qty for ABC
on 7/3, etc. Make sense?

"Mike H" wrote:

Jennifer

You don't need an array formula, try this:-

=SUMPRODUCT(($A$2:$A$6=A10)*($B$1:$E$1=B10)*($B$2: $E$6))

with a10 = abc
b10 = the date you are looking for

"Jennifer" wrote:

Hello smart folks.

I have a spread sheet that looks like ths:

Item Number Jul-03 Aug-03 Sep-03 Oct-03
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

I need to fil the information as follows:

7/03 ABC Qty
8/03 ABC Qty
7/03 CDE Qty
8/03 CDE Qty

etc. Can someone please help?