1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Help with T-shirt order

    From Diane Peifer@21:1/5 to All on Thu Sep 16 07:59:16 2021
    I have created a T-shirt order form. Here is the link https://docs.google.com/spreadsheets/d/154yG2v7UbiKsuR5vaoGHxa-fwh99aEp_AYMTd5nR_IQ/edit?usp=sharing

    I have successfully used the countif function to total number of orders for each size. But what I really need is to somehow multiply by the quantity in those rows to get the accurate number of each size. Can anyone help?

    Thanks,
    Diane Peifer

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Fri Sep 17 13:04:01 2021
    In article <7e55ab31-e974-4345-8460-e45732aac396n@googlegroups.com>, mikedianepeifer@gmail.com says...

    I have created a T-shirt order form. Here is the link https://docs.google.com/spreadsheets/d/154yG2v7UbiKsuR5vaoGHxa-fwh99aEp_AYMTd5nR_IQ/edit?usp=sharing

    I have successfully used the countif function to total number of orders for each size. But what I really need is to somehow multiply by the quantity in those rows to get the accurate number of each size. Can anyone help?

    Thanks,
    Diane Peifer

    I think you already have that, don't you? The SumIf function is summing the values in Quantity for those entries which match the particular value in the Size column. I checked manually (!) for 'S' and 'M' and the figures in your 'Sizes' table are correct.

    Looking at your worksheet, can I offer two suggestions?

    T-shirts all seem to cost $15. Yet Tina H's T-shirt appears to cost $14.08.
    in reality (and guided by the comment) she's been given a discount (or has paid) $0.92. It's often easier to record that explicitly, so instead of having Y/N in your Paid column, just put how much has been paid. If needed, a further column could calculate any remaining balance - even if someone has overpaid, which happens! It's rarely a good idea to 'flex' your data design to accomodate an exceptional case - instead, tend to record any additional data necesary to make it all make sense.

    More generally, have you explored Pivot Tables? Sure, you can do wonders with Excel/Sheet's battery of logical and lookup functions, but Pivot Tables, once you've got the idea of them, do it all for you. You add a Pivot Table to display the breakdown you want, and you can add multiple Pivot Tables (possibly on a separate worksheet if preferred) for different purposes.

    This excellent video from one of the best online trainers will get you started. Many people scratch their heads over Pivot Tables for the first few minutes, but then the penny drops, and they never look back.

    https://www.youtube.com/watch?v=UsdedFoTA68

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)