Is it possible? excel

Discussion in 'Mac Apps and Mac App Store' started by zamudiovsky, Aug 9, 2013.

  1. zamudiovsky macrumors regular

    Joined:
    Sep 17, 2012
    #1
    I need to create invoices for all my customers (about 60) from an excel file I export from the system, currently I have to input the values manually and it takes me about 4 to 5 hours on sunday nights and I'm tire of doing it like that to be honest…
    I was wondering if there is an IF statement to do the following:
    File A:
    ------A---------B---
    1 | Store 1 - $120
    2 | Store 1 - $100
    3 | Store 1 - $20
    4 | Store 2 - $75
    5 | Store 2 - $44
    6 | Store 3 - $150
    7 | Store 3 - $10

    on file B:
    I need to make a sum of all of the values on cell B from File A if they are from Store 1…and the same for store 2 and 3…

    So it would be something like If Column A say "Store 1" then sum all of the values on column B
    and the same for Store 2 and 3…

    Is it possible to do that?

    I hope you guys can help me out, thanks!!
     
  2. Flyingpig macrumors newbie

    Joined:
    Jan 2, 2005
    #2
    SumIf is your friend.
     
  3. Zerthis macrumors newbie

    Joined:
    Jul 28, 2013
    #3
    If field A1 is your first store and field B1 is the amount, copy this in field C1:

    =SUMIF($A$1:A1;A1;$B$1:B1)

    (and drag the formula to all other fields below).

    The result will look like:
    Code:
    A       B       C
    Store 1	120	120
    Store 1	100	220
    Store 1	20	240
    Store 2	75	75
    Store 2	44	119
    Store 3	150	150
    Store 3	10	160
    
    The last entry of any store will show you the cumulative amount. Will also work if column A is not sorted, but you might want to keep it sorted to spot the final (or highest) amount more easily.
     
  4. zamudiovsky thread starter macrumors regular

    Joined:
    Sep 17, 2012
    #4
    Thanks for your answer, as soon as I get home I will try this!

    Does it work even if the I have more transactions? Every week it's different, Store 1 might do 120 transactions one week and 34 the following...and the excel I export comes all unsorted and with extra information, but I can just delete the extra information and sorted, that is no problem haha

    Thanks again, as soon as I test that I will come and thank you some more haha
     
  5. Zerthis, Aug 9, 2013
    Last edited: Aug 9, 2013

    Zerthis macrumors newbie

    Joined:
    Jul 28, 2013
    #5
    For example:
    Code:
    A       B       C
    Store 1	120	120
    Store 1	100	220
    Store 1	20	240
    Store 2	75	75
    Store 2	44	119
    Store 3	150	150
    Store 3	10	160
    Store 1	300	540
    Store 5	60	60
    Store 1	55	595
    Store 3	22	182
    Store 2	31	150
    Store 3	25	207
    Store 9	65	65
    Store 8	65	65
    Store 3	15	222
    Store 3	16	238
    Store 6	54	54
    Store 1	77	672
    Store 3	100	338
    Store 8	25	90
    Store 9	50	115
    Store 6	22	76
    
    So yes it works exactly like you want it to work. You do not need to delete or rearrange any of the columns, as long as the formula corresponds with the fields you want to calculate. Every new entry of "Store #" will give you the new cumulative amount.

    Anyway, if you choose to sort (ascending) the result will look like this:
    Code:
    A       B       C
    Store 1	120	120
    Store 1	100	220
    Store 1	20	240
    Store 1	300	540
    Store 1	55	595
    Store 1	77	672
    Store 2	75	75
    Store 2	44	119
    Store 2	31	150
    Store 3	150	150
    Store 3	10	160
    Store 3	22	182
    Store 3	25	207
    Store 3	15	222
    Store 3	16	238
    Store 3	100	338
    Store 5	60	60
    Store 6	54	54
    Store 6	22	76
    Store 8	65	65
    Store 8	25	90
    Store 9	65	65
    Store 9	50	115
    
     
  6. RUGERMAN macrumors regular

    Joined:
    Jun 12, 2010
    #6
    Pivot table

    Try looking at how to create a "pivot table" I've done this with similar situations years ago and it worked well.
     
  7. Pharmscott, Aug 9, 2013
    Last edited: Aug 9, 2013

    Pharmscott macrumors 6502a

    Pharmscott

    Joined:
    Dec 13, 2011
    Location:
    Sacramento, CA
    #7
    Yes, pivot table. SumIf is a pain to set up. Pivot tables do this automatically. With the OPs data, you could have results in a couple of minutes. Happy to help OP set one up if you like.

    Check this post: http://forums.macrumors.com/showthread.php?t=1586036&highlight=excel
     

Share This Page