Auto Adding

[appletonthecat]

Hey Guys 

i am looking to make a task auto

hopefully, you can make sense of the idea below

column A has a list of names, say 200

Column b is a total amount 

Column C I add a name from the list of column A (this can be multiple times in the rows)

Column d has a price that i insert  

how do I make column B look at  column C find the correct name in column A and add the price from column D to Column B

i have tried to make a table below with the idea 

Cheers

dave

Column A     Column B   Column C     Column D   

dave           4,00             Jo                 1,00    

jane            0.00             jo                 1,00

Pete            0.00            Dave             1,00

jess             1,00            jess               1,00

anna            0.00           Jo                  1,00

jo               3,00            dave              1,00

                                    dave              1,00

                                    dave              1,00

[#2 · Accepted solution]

@appletonthecat 

=SUMIF($C$2:$C$9,A2,$D$2:$D$9)

See the attached sheet. And here's a resource on the SUMIF function.

[#3]

@mathetes 

oh my god that was so quick, thank you so much, i have no idea how you did that but it was exactly what i was looking for  can I push my luck with one (ish) last question 

how would i add more coloums to B

say columns C and D were January and columns E and F were February how do i add that to columns B as well 

i tried this but didn't work 

($C$2:$C$100;A2;$D$2:$D$100)+($E$2:$E$100;A2;$F$2:$F$100)

Cheers for the help

dave

[#4]

@appletonthecat 

You don't keep adding columns for different months. Instead create a single table, as you'll see I've done in this revised attachment, and you'll be able to add rows indefinitely, new names, new months, varied quantities.

And you switch to a Pivot Table to summarize it. Or at least, if I'm correctly hypothesizing where you're going with this. The Pivot Table is a very powerful and very popular tool that's been around for decades in the spreadsheet world. It's ideal for summarizing the kind of data you're starting to describe.

Create a single table, as I've done; you'll be able to add rows indefinitely, and Pivot Table will keep up with those additions (just need to use the "Refresh Data" capability which you've find on the Data Toolbar as well as in the Data menu (across the top).

If I misunderstood the direction you are going in, please come back and correct my understanding.

[#5]

Hey,
You are clearly an expert, and my level is not great I am a little lost with the last info you sent me but am very grateful

Is there a way to use the sheet I have started
I have attached the sheet I am working on to help with the idea
Column A is the list of shops
Starting at column O11 with Q11
Column s11 with u11
Column x11 with z11

Then columns Q U Z add to column B
Then I can use the same formula for the other months?

Thanks once more

Cheers

Dave

ok i am trying to figure out how to attach the file

[#6]

@appletonthecat 

You have several choices regarding your file. My preference would be that you put a copy in OneDrive or GoogleDrive and then add a link and grant access to me and others on this forum here in a reply.

The other method would be to send me a personal message by clicking on my username, going to the profile, and sending a private message with the options there present. I prefer the former because it keeps the exchange public, where others can offer their expertise as well.

[#7]

@appletonthecat 

I"ll take another look at the file in the morning, but I'm pretty sure I'm going to come back and suggest a radical reorganization of it.

First I have to figure out what it all IS in the first place. You're familiar with it, so it may seem obvious to you. What, for example, are the various line entries under each month, under the headings of "Takings" and "Costs"--as well as the section in the middle that seems to lack a heading other than the month name, which presumably applies to all three sections? 

But very seriously, this may be a way to organize data so that you as a human can look at it and make sense of it, but it's NOT a way to organize it to take optimum advantage of Excel. Assuming it has something to do with tracking income and/or expenses on a monthly basis, you'd be FAR better off learning to create it as a transactional database--same data you have now, just keeping it all as one table, letting the dates alone take care of whether it's the month of Jan or Feb or Mar.....

I'm attaching yet another example of how that could work, letting the Pivot Table do all the heavy lifting. See if it makes sense. You can learn to do it later--not as hard as you might think. In fact, a LOT easier than making what you have so far work and work reliably.  Notice the sheet called "Raw Data" and then the one labelled "Summary."  ALL of the summarized data on the summary sheet was handled by the Pivot Table tool; not a single formula had to be entered. That's how powerful the tool is. And as you can see, it's broken out by month and category......

[#8]

@mathetes

Hey

It's really amazing how much you don’t know until you have an idea about making things faster and then the new learning process starts, by the looks of it I need to really learn and understand Pivot Table

I am new to excel and noticed that I am doing what people called brute force formulas and have looked at other options but was getting a little lost trying to make it work, I am up for change as this is the new excel document for next year as the way I am doing it seemed to take forever (but still quicker than the year before) and watching people do many tricks and tips on tic tok I know a change is needed  

To answer a couple of questions about the sheet

Column A is the shop and companies I use all the time in my business

Column O9 where it says MB means these are transactions I pay from my bank account online

Column X9 Cash means I paid by card in the shop and got a tax recipe

Column X9 CASH means I paid by card and got no recipe

I do it this way so when I am missing something I know exactly how I paid and when

The Takings O5 are from other sheets that just let me know how much I have taken in the month

The Cost is Q7+U7+Z7 just added together

This just makes it easy for me to see if I am spending too much that month

The section in the middle

Was just a place so I could see the year in full instead of going across the page each time to see how much I spent on MB in say March or CASH no recipes in June

Once again I can't thank you enough for your time on this subject as I am sure there are lots of people like me in need of guidance

Cheers

Dave