Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT where arrays have different dimensions
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT where arrays have different dimensions
=SUMPRODUCT(--(Sheet1!$B$2:$D$2=$A2)*(Sheet1!$A$5:$A$9=C$1)*(She et2!$B$5:$D$9))
Data on Sheet1, output on (say) Sheet2 Put in C2 and copy across HTH "Chas" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT where arrays have different dimensions
On Jul 23, 3:48 pm, Chas wrote:
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT where arrays have different dimensions
On Jul 23, 7:41 pm, ed wrote:
On Jul 23, 3:48 pm, Chas wrote: 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 shouod chaqnge by using the44e new date "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- 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT where arrays have different dimensions
Thanks, worked like a charm. Not sure what i was doing wrong.
"Toppers" wrote: =SUMPRODUCT(--(Sheet1!$B$2:$D$2=$A2)*(Sheet1!$A$5:$A$9=C$1)*(She et2!$B$5:$D$9)) Data on Sheet1, output on (say) Sheet2 Put in C2 and copy across HTH "Chas" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUMPRODUCT with arrays | Excel Discussion (Misc queries) | |||
Sumproduct, two factors, wildcard, and three dimensions... is there a way? | Excel Worksheet Functions | |||
Sumproduct or ??? for non-same dimensions | Excel Discussion (Misc queries) | |||
Sumproduct arrays | Excel Discussion (Misc queries) | |||
Problem with SUMPRODUCT and Arrays | Excel Worksheet Functions |