Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas or VBA?
Try something like this:
Using your data structure..... Qty in rows 2,5,8,....etc Model in rows 3,6,9 etc As long as the Qty is ALWAYS in the row above the model Then.... B100: 120 C100: =SUMIF($C$3:$C$98,$B100,$C$2:$C$97) B101: 125 C101: =SUMIF($C$3:$C$98,$B101,$C$2:$C$97) Notice that the Range argument is offset from the Sum_Range argument by 1 cell Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Mark Ivey" wrote: I have a problem with a weekly sales report. There are 5 possible orders in a given day. Some of these orders may have the same Model number. My manager wants to create a totals table below each day showing how many of each Model we had on that day. I tried to figure out how with nested IF's, but it got scary and still didn't work out right. I finally made a pretty large table on another sheet that compares each order (one at a time) for ONE Model at a time. Then reporting back the total for that model back to the original Totals section. Does anyone know of a formula that would work with this scenario, or should I hang the formulas out to pasture and fix it up with VBA? Please see attached for a picture of the table. -- Mark Ivey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas or VBA?
Ron,
I had not thought of using the SUMIF with an offset. I think that will work. There is actually other data in the same column for other information about the Model, but I think the offset you mentioned will take care of that problem. Thanks a million... -- Mark Ivey "Ron Coderre" wrote in message ... Try something like this: Using your data structure..... Qty in rows 2,5,8,....etc Model in rows 3,6,9 etc As long as the Qty is ALWAYS in the row above the model Then.... B100: 120 C100: =SUMIF($C$3:$C$98,$B100,$C$2:$C$97) B101: 125 C101: =SUMIF($C$3:$C$98,$B101,$C$2:$C$97) Notice that the Range argument is offset from the Sum_Range argument by 1 cell Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Mark Ivey" wrote: I have a problem with a weekly sales report. There are 5 possible orders in a given day. Some of these orders may have the same Model number. My manager wants to create a totals table below each day showing how many of each Model we had on that day. I tried to figure out how with nested IF's, but it got scary and still didn't work out right. I finally made a pretty large table on another sheet that compares each order (one at a time) for ONE Model at a time. Then reporting back the total for that model back to the original Totals section. Does anyone know of a formula that would work with this scenario, or should I hang the formulas out to pasture and fix it up with VBA? Please see attached for a picture of the table. -- Mark Ivey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas or VBA?
Thanks for the feedback.....I'm glad that worked for you.
*********** Regards, Ron XL2002, WinXP "Mark Ivey" wrote: Ron, I had not thought of using the SUMIF with an offset. I think that will work. There is actually other data in the same column for other information about the Model, but I think the offset you mentioned will take care of that problem. Thanks a million... -- Mark Ivey "Ron Coderre" wrote in message ... Try something like this: Using your data structure..... Qty in rows 2,5,8,....etc Model in rows 3,6,9 etc As long as the Qty is ALWAYS in the row above the model Then.... B100: 120 C100: =SUMIF($C$3:$C$98,$B100,$C$2:$C$97) B101: 125 C101: =SUMIF($C$3:$C$98,$B101,$C$2:$C$97) Notice that the Range argument is offset from the Sum_Range argument by 1 cell Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Mark Ivey" wrote: I have a problem with a weekly sales report. There are 5 possible orders in a given day. Some of these orders may have the same Model number. My manager wants to create a totals table below each day showing how many of each Model we had on that day. I tried to figure out how with nested IF's, but it got scary and still didn't work out right. I finally made a pretty large table on another sheet that compares each order (one at a time) for ONE Model at a time. Then reporting back the total for that model back to the original Totals section. Does anyone know of a formula that would work with this scenario, or should I hang the formulas out to pasture and fix it up with VBA? Please see attached for a picture of the table. -- Mark Ivey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming |