SUMIF - can it be used here?

[excelcatt]

Hello all!

First post so go easy!.

I am trying to setup a Treasurer's spreadsheet using Excel.

Found a great template here, that I can modify slightly to make work:

http://unicentre.uow.edu.au/content/...latestReleased

OR

http://preview.tinyurl.com/k6unucr

The Problem is the SUMIF functions on the 'Total Budget" sheet.

The way it has been written it cannot work, as (1) the criteria it is trying to find moves dynamically down a row as new data isentered via the drop down box; (2) the criteria needs to 'look' for one of [4] of the criteria in the dropdown box and match.

Is there anyway I can get this going, I have tried, but cannot using the 'SUMIF' function. Or am I on the wrong tram here with this....

Thanks in anticpation.

excelcatt...

[excelcatt]

Added screenshots for those that do not want to download the file - not sure what the convention is here; apologies in advance:

Screenshot from 2014-01-15 11:13:58.jpg

Screenshot from 2014-01-15 11:17:55.jpg actual formula is pasted below table, it's a bit hard to read in formula bar

Thanks.

[FDibbins]

Hi and welcome to the forum

The forum allows - and we prefer- you to upload sample workbooks here

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[excelcatt]

Ok. Thanks got that.

I have uploaded a file with some data in it.

I am still stumped on this.

Starting to think SUMIF is not the formula I need...please chime in if you have any opinions!

Thanks.

[FDibbins]

The sumif() seems to be working just fine? What were you expecting, and where are there errors?

[excelcatt]

Thanks for looking at the sheet.

When I input other values from other selections on the drop down box, it errors. It does not add the correct amount(s) from the corresponding selections made from the drop down box (on both 'Income' & 'expenses' sheets) that are on 'Total Budget' sheet - am I making sense?

For example in the example above, it's not adding up corectly for 'other ouflows' which are none that have been inputed into the 'expenses' sheet, but showing $155 on the 'Total Budget' sheet! Same for 'Event expenses' on 'expenses' sheet. (should be $50 - not $155).

You can also tryu and input some data into the income sheet and it does the same thing, the categories in the drop down box;

Memberships
BBQ Income
Event Income
CSE Funding
Sponorship/Donations
Other Income

don't add correctly in respective categories(the 'criteria' in the SUMIF formula) when SUMIF formula executes on that data in Column D on sheet 'Income"

Thanks for all your help. This is great resource for Excel!

:-)

[FDibbins]

You have the middle reference in your sumif() pointing to the wrong place. It needs to point to the description opposite where the total is...
=SUMIF(Income!$E$9:$E$52,A10,Income!$D$9:$D$52)
and
=SUMIF(Expenses!$B$8:$B$33,A19,Expenses!$E$8:$E$33)

Also you need to "fix" (absolute) the other ranges so they dont adjust as you copy down

[excelcatt]

You have the middle reference in your sumif() pointing to the wrong place. It needs to point to the description opposite where the total is...
=SUMIF(Income!$E$9:$E$52,A10,Income!$D$9:$D$52)
and
=SUMIF(Expenses!$B$8:$B$33,A19,Expenses!$E$8:$E$33)

Also you need to "fix" (absolute) the other ranges so they dont adjust as you copy down

Thanks very much for that! I was kicking myself at such a simple error that I did not pick up!

Again many thanks!