Formulas or VBA?

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

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

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" <[email protected]> wrote in message news:[email protected]...
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

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" <[email protected]> wrote in message news:[email protected]...
> 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