SUMPRODUCT where arrays have different dimensions

[Chas]

Hi, not sure if this is possible or not just thougt I would check
I have data set up in the following Format
A B C D
Labor Category
3130 3140 3120
Date to begin PM Acct Eng

02/01/08 40 29 20
03/01/08 15 27 100
05/01/08 30 55 72
02/01/08 32 17 87
05/01/08 2 93 47

I need a formula that find the labor category in question, then sum the
numbers for particalur month. The out put will appear as:
A B C D E F
02/01/08 03/01/08 04/01/08 05/01/08
3130 PM 72 15 0 32

So in Cell C2 i want a formula to find A2 and sumif the "date to begin" is
equal to C1.

I hope this makes since, if not just ask & I can try to clarify. I've tried
using the sumproduct but it won't work because of the different dimensions.
I'm not opposed to using a macro, i'm just not versed enough to write one.

Any help is greatly appreciated. Thanks in advance!

 

[Toppers]

=SUMPRODUCT(--(Sheet1!$B$2:$D$2=$A2)*(Sheet1!$A$5:$A$9=C$1)*(Sheet2!$B$5:$D$9))

Data on Sheet1, output on (say) Sheet2

Put in C2 and copy across

HTH

 

[ed]

Hi, not sure if this is possible or not just thougt I would check
I have data set up in the following Format
A B C D
Labor Category
3130 3140 3120
Date to begin PM Acct Eng

02/01/08 40 29 20
03/01/08 15 27 100
05/01/08 30 55 72
02/01/08 32 17 87
05/01/08 2 93 47

I need a formula that find the labor category in question, then sum the
numbers for particalur month. The out put will appear as:
A B C D E F
02/01/08 03/01/08 04/01/08 05/01/08
3130 PM 72 15 0 32

So in Cell C2 i want a formula to find A2 and sumif the "date to begin" is
equal to C1.

I hope this makes since, if not just ask & I can try to clarify. I've tried
using the sumproduct but it won't work because of the different dimensions.
I'm not opposed to using a macro, i'm just not versed enough to write one.

Any help is greatly appreciated. Thanks in advance!

The formula to put in C2 is =SUMIF($A$x:$A$y,"02/01/08",$B$B$x:$B$y).
where x and y are the top and bottom rows of information. The formul
in B2 is the same but with C instead of B.

I don't know what you mean by dimensions as the column length need not
be the same. You probably did it right but didn't put the " "
around your find.

ed

 

[ed]

The formula to put in C2 is =SUMIF($A$x:$A$y,"02/01/08",$B$B$x:$B$y).
where x and y are the top and bottom rows of information. The formul
in B2 is the same but with C instead of B.

I don't know what you mean by dimensions as the column length need not
be the same. You probably did it right but didn't put the " "
around your find.

ed- Hide quoted text -

- Show quoted text -

Sorry: I thnk I lost a line while editing my response. The formula in
D2 (not B2 as I first said)should be to change the date to
"03/01/08". Same change for columns E,F etc. Your next Row will have
the next Catogory and the letter in the end of my formula will change
from B to C to D, etc to read each successive column.

I don't know your sheet layout but presume you can handle that once
you get a handle on the " " bit.


ed

 

[Chas]

Thanks, worked like a charm. Not sure what i was doing wrong.