formula

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
=SUMIF(List_of_codes,Code_you_wish_to_sum,List_of_amounts)

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
hi guys can anyone else help on this?

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
Here all is Ok. Like you can see in my last post.

Could you post the formula that you used?

Markmzz

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

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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