Is it possible? excel

[zamudiovsky]

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!!

 

[Flyingpig]

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!!

SumIf is your friend.

 

[Zerthis]

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:

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.

 

[zamudiovsky]

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:

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.

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

 

[Zerthis]

For example:

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:

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

 

[RUGERMAN]

Pivot table

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

Try looking at how to create a "pivot table" I've done this with similar situations years ago and it worked well.

 

[Pharmscott]

Try looking at how to create a "pivot table" I've done this with similar situations years ago and it worked well.

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: https://forums.macrumors.com/threads/1586036/

 

[ashira]

you can find your answer @ https://www.facebook.com/groups/exceladvise