formula

[marf85]

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

 

[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

Ok.

Try this small modification (in red) in the formula.

In H4 (Sheet 1) and copy down

=SUMIF('Sheet 2'!$B$1:$B$147,G4,'Sheet 2'!$C$1:$C$147)
​

Markmzz

 

[marf85]

Ok.

Try this small modification (in red) in the formula.

In H4 (Sheet 1) and copy down

=SUMIF('Sheet 2'!$B$1:$B$147,G4,'Sheet 2'!$C$1:$C$147)
​

Markmzz

still the same, pops up the open file box and asks me to update the values in sheet 2?

 

[markmzz]

still the same, pops up the open file box and asks me to update the values in sheet 2?

Hi!

Look at your Inbox Private Message.

Markmzz

 

[marf85]

@markmzz
what a genius, thanks for resolving this big headache!

 

[markmzz]

Hi!

Now I see what you want.

Try the small modification of your formula below:

=SUMIF(PURCHASE!$G$4:$G$450,B1,PURCHASE!$F$4:$F$450)

Or

=SUMIF(PURCHASE!$G$4:$G$450,"*"&B1&"*",PURCHASE!$F$4:$F$450)

Markmzz

 

[markmzz]

@markmzz
what a genius, thanks for resolving this big headache!

You're welcome and thank you for the feedback.

Markmzz