| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult Calculations
I am needing to combine multiple functions, I believe, but am in need of some
help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
Try this:
=SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'! B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) On Dec 10, 12:55 pm, Amber wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
=SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data
2007'!B2:B64868="XIAPPLXRP110"),'Raw Data 2007'!D2:D64868) "Amber" wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
I tried this but received #N/A
Not sure why. "Teethless mama" wrote: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110"),'Raw Data 2007'!D2:D64868) "Amber" wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
This only gave me #N/A. Not sure why!
"ilia" wrote: Try this: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'! B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) On Dec 10, 12:55 pm, Amber wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
Well, both our responses are pretty much identical, so that stands to
reason they both returned the same error. The only way I can think of to get an #N/A error out of a SUMPRODUCT is if your number of rows doesn't match up. Double-check the formula and make sure that all references start on row 2 and end on row 64868. FWIW, I used your sample data and got what appear to be correct answers from either formula. On Dec 10, 1:43 pm, Amber wrote: This only gave me #N/A. Not sure why! "ilia" wrote: Try this: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'! B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) On Dec 10, 12:55 pm, Amber wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. - Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
"ilia" wrote: Well, both our responses are pretty much identical, so that stands to reason they both returned the same error. The only way I can think of to get an #N/A error out of a SUMPRODUCT is if your number of rows doesn't match up. Double-check the formula and make sure that all references start on row 2 and end on row 64868. FWIW, I used your sample data and got what appear to be correct answers from either formula. On Dec 10, 1:43 pm, Amber wrote: This only gave me #N/A. Not sure why! "ilia" wrote: Try this: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'! B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) On Dec 10, 12:55 pm, Amber wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. - Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
Here is the formula.... =SUMPRODUCT((MONTH('Raw Data
2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) I am using 2 separate tabs. Could that cause the problem? "ilia" wrote: Well, both our responses are pretty much identical, so that stands to reason they both returned the same error. The only way I can think of to get an #N/A error out of a SUMPRODUCT is if your number of rows doesn't match up. Double-check the formula and make sure that all references start on row 2 and end on row 64868. FWIW, I used your sample data and got what appear to be correct answers from either formula. On Dec 10, 1:43 pm, Amber wrote: This only gave me #N/A. Not sure why! "ilia" wrote: Try this: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'! B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) On Dec 10, 12:55 pm, Amber wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. - Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Difficult Calculations
Following Biff's stategy, here it is in action:
http://www.44lbs.net/ilia/sumproduct.jpg Here is what the source data looks like (some random values): http://www.44lbs.net/ilia/rawdata2007.jpg It's populated as far down as your formula calls for: http://www.44lbs.net/ilia/rawdata2007_tailEnd.jpg Pivot table confirms formula result: http://www.44lbs.net/ilia/rawdata2007_pivot.jpg Hope that helps. On Dec 10, 4:56 pm, Amber wrote: Here is the formula.... =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) I am using 2 separate tabs. Could that cause the problem? "ilia" wrote: Well, both our responses are pretty much identical, so that stands to reason they both returned the same error. The only way I can think of to get an #N/A error out of a SUMPRODUCT is if your number of rows doesn't match up. Double-check the formula and make sure that all references start on row 2 and end on row 64868. FWIW, I used your sample data and got what appear to be correct answers from either formula. On Dec 10, 1:43 pm, Amber wrote: This only gave me #N/A. Not sure why! "ilia" wrote: Try this: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'! B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D2:D64868)) On Dec 10, 12:55 pm, Amber wrote: I am needing to combine multiple functions, I believe, but am in need of some help. I currently have a document that I run a sumproduct. With the calculations that I now need, I am not sure how to combine the sumproduct with a sumif function to obtain totals. My data sampling...... Date Creator Order # PreTaxed Amount 1/1/2007 XIAPPLXRP110 413457 231.54 1/17/2007 AVISD 413558 78.98 1/28/2007 SMITHA 413563 149.74 2/2/2007 XIAPPLXRP110 413573 42.41 2/5/2007 AVISD 413575 38.54 3/4/2007 HILTONA 413580 103.48 4/6/2007 AVISD 413584 28.37 9/1/2007 XIAPPLXRP110 413586 62.72 My current calculation: =SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")) This calculation is to determine how many orders we have coming in via XIAPPLXRP110 each month. Now I would like to add another layer...I want to know the totals of the orders coming in via XIAPPLXRP110, by the month rather than just counting the number of orders. Any help on this would be great. I need to get this ASAP for my emloyer. - Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difficult function | Excel Worksheet Functions | |||
| Difficult Formula | Excel Worksheet Functions | |||
| Help, too difficult for me. | Excel Worksheet Functions | |||
| Too difficult for me, please help. | Excel Worksheet Functions | |||
| Difficult but do-able? | Excel Discussion (Misc queries) | |||
Linear Mode
