formula

M

marf85

New Member
Joined
Apr 20, 2018
Messages
8
Hi all,
ive designed a spreadsheet for my accounting and im after a little help if anyone can please.

within my sheet i have created a column with a drop down containing all the nominal codes. on sheet 2 which is my working sheet for formulas, i have the full list of codes. now, if a code is used in the accounting side, i would like a total kept next to the nominal codes on sheet 2. so at the end of the month i can see exactly how much has been spent against each nominal code. ive exhausted my skills though and just cant make a formula work, anyone able to help? please?

thanks
Jez
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
njimack said:
=SUMIF(List_of_codes,Code_you_wish_to_sum,List_of_amounts)
Click to expand...

Thanks njimack, i still cant get it to work though. heres what ive put

=SUMIF(B1:B147,Sheet1!G4:G70)

page 1 has the accounting sheet and page 2 is the working sheet with the nominal codes listed down the columns. i may need a more simple explanation? many thanks
 
Upvote 0
marf85 said:
hi guys can anyone else help on this?
Click to expand...

Hi!

Try this in H4 (Sheet1) and copy down

=SUMIF(Sheet2!$B$1:$B$147,G4,Sheet2!$C$1:$C$147)


[TABLE="class: grid, width: 490"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sheet1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code001[/TD]
[TD="align: center"]835[/TD]
[TD="align: center"]Sheet2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code002[/TD]
[TD="align: center"]879[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]List[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code003[/TD]
[TD="align: center"]619[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code001[/TD]
[TD="align: center"]8302[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code004[/TD]
[TD="align: center"]345[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code002[/TD]
[TD="align: center"]7667[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code005[/TD]
[TD="align: center"]519[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code003[/TD]
[TD="align: center"]7240[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code006[/TD]
[TD="align: center"]162[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code004[/TD]
[TD="align: center"]9100[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code007[/TD]
[TD="align: center"]606[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code005[/TD]
[TD="align: center"]6928[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code008[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code006[/TD]
[TD="align: center"]7377[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code009[/TD]
[TD="align: center"]521[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code007[/TD]
[TD="align: center"]6320[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code010[/TD]
[TD="align: center"]310[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code008[/TD]
[TD="align: center"]6489[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code001[/TD]
[TD="align: center"]612[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code009[/TD]
[TD="align: center"]7895[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code002[/TD]
[TD="align: center"]781[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code010[/TD]
[TD="align: center"]7563[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Code003[/TD]
[TD="align: center"]747[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]***[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]******[/TD]
[TD="align: center"]********[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
 
Last edited:
Upvote 0
markmzz said:
Here all is Ok. Like you can see in my last post.

Could you post the formula that you used?

Markmzz
Click to expand...

i copied your formula direct and planned to edit it. this time it comes up with an open box asking me to update values in sheet 2
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
User Select PDF import to specific Excel sheet
2 3 4
Replies
31
Views
1K
John_w
J
R
  • Question Question
Time sheet formulas
Replies
2
Views
374
rodnaaaaay
R
S
  • Question Question
Icon Set or Arrows Conditional Formatting
Replies
2
Views
876
Seamus123
S
I
  • Solved
After code is run i am unable to use keyboard arrows to move from selected cell
Replies
3
Views
179
ipbr21054
I
P
  • Solved
Dynamic Sheet Tabs in Index Match function
Replies
9
Views
295
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,830
Messages
6,181,227
Members
453,025
Latest member
Hannah_Pham93

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top