SUMIF Problem

Well having the data in lots of columns is not the best way to do it, for arranging the data, they would be better in longer columns and less columns, the date should also really be a date in the totals tab, but as you have written it the following is what you want.

Please Login or Register  to view this content.

Thanks Ron/Dav, those formulas worked a treat. Though I have been playing around with them for a while and can’t get it working. I’m also not sure what to do as the spreadsheet I’m trying to replicate this on is continuous and jobs will be constantly added to it.

I have attached my company’s project bank to see if you could help by applying the same formula to it. At present when a salesman wins a job he logs it into the corresponding spreadsheet (depending on the month). Then our accounts payable copies this into the ‘current orders’ spreadsheet. Problem with this is that in the past some haven’t copied over properly. On a couple of occasions we didn’t even bill the customer for the work, they had to tell us that they wanted to pay.

I want to try and use the current orders and totals spreadsheet only. As you can see the current setup is not ideal. I have had to delete sensitive information so apologies for the sheet looking so empty.

Again thanks very much for you help

Sam

Well what do you want to do? You can perform the same calculations on the current orders as the month sheets to check if any of the data is missing, and perhaps return the order numbers of those records which are.

Why can the data not be entered directly onto the current orders sheet?

Also how can you tell from the sheet if an invoice has been sent/or paid. do you need a flag to say when it has been paid or sent?

Regards

Dav

Hi Dav,

The only reason we have the individual months sheets were because no one could write a formula that could add up the jobs in the 'current orders sheet' by recognising what month they were logged.

All that was asked of me was to create formulas that would mean that we could delete the 'individual months' sheets.

So the aim is for the current orders sheet and totals sheet to be the only ones present in the file. All the jobs will be logged on the 'current orders' sheet and the totals page will add up all the invoices (inc 1st, 2nd, 3rd, 4th invoice) that correspond to each month.

**Please note I do want all the data to be entered into the current orders sheet ONLY. Just cant think of a formula that will replicate what the spreadsheet is doing now.**

I'm not sure how our credit controller knows when an invoice has been paid/sent. He called in sick today so I couldn’t ask him . From what I can gather he arranges with the customer when the invoicing will be received, he then fills in the Predicted Invoice Month column in 'current orders', the Invoice Value Outstanding column adds what is left to invoice. He would then chase the customer to get them to pay on/or after the date agreed. That’s why so many of our customers pay late. Some sort of a flagging system would be great!!!

Do you think it is possible to do everything the spreadsheet is doing now but only having “Current Orders’ and ‘Totals’???

Thanks for all your help!!!

Regards

Sam

yes the way to go is probably sumproduct which extends the function of sumif to multiple ifs

the condition for you will be a greater than or equal to the current month and less than the next month, also perhaps with a flag column to indicate if the invoice has been paid.

http://www.excel-examples.com/excel-...sumproduct.htm

will hopefully give you some idea

Regards

Dav

Hi,

Cheers for the help but I have been struggling with this for the last couple of hours but have got nowhere. Could someone solve this for me

Thanks

Sam