Excel Question

[Shaun @ Tru6]

I have a spreadsheet of customers and their orders this season. One column is Status (blank or PAID text) and next to it is the invoice amount.

I want a sum of paid and unpaid invoices that changes only by typing PAID in the Status column.

Can this be done? I don't want a row by row summation of IF statements. IF(A2 ="PAID",B2,0) + IF(A3...

[Aggie93]

=SUMIF(row of status,"paid",row with $)

Originally Posted by Shaun 84 Targa

I have a spreadsheet of customers and their orders this season. One column is Status (blank or PAID text) and next to it is the invoice amount.

I want a sum of paid and unpaid invoices that changes only by typing PAID in the Status column.

Can this be done? I don't want a row by row summation of IF statements. IF(A2 ="PAID",B2,0) + IF(A3...

[Shaun @ Tru6]

THANK YOU!

=SUMIF(E2:E116,"paid",G2:G116)

Works perfectly!

[wdfifteen]

That's cool. I had no idea Excel was so powerful. This brings back memories of writing "if then else" lines in FORTRAN - on punch cards.

[berettafan]

suggest considering quickbooks for a/r if not already doing. it can do late fees, monthly statements, etc. very easily. also exports reports to excel.

can even email statements/invoices to customers.

[Shaun @ Tru6]

Originally Posted by wdfifteen

That's cool. I had no idea Excel was so powerful. This brings back memories of writing "if then else" lines in FORTRAN - on punch cards.

I was considering writing some visual basic but thought Excel must have come along way by now, thanks again to Aggie!

Originally Posted by berettafan

suggest considering quickbooks for a/r if not already doing. it can do late fees, monthly statements, etc. very easily. also exports reports to excel.

can even email statements/invoices to customers.

Excellent suggestion, I use QB for all bookkeeping but we created a custom Access db for our order entry and invoicing that also does inventory. We did this before we bought QB and someday I'll figure out how to create seasonal SKUs in QB so it's all in one system and I won't have to jump through these hoops. What I like about the current one is generates nice reports, exactly as I want them which can be exported to Excel. So I have to invest the time into quickbooks to generate the same reporting, and I am sure if will even be better than what we have now.

[rusnak]

Access should do it better than Excel. The problem is Access is so damn hard to use. I prefer FileMaker Pro.

[Shaun @ Tru6]

I used to love Filemaker Pro back when it was flat in the early Mac days. But I had to learn VB for a short term career change and the only thing that made it worthwhile was learning and working with recordsets using SQL. I came to love relational db architecture and efficiently moving/showing data around it, dynamically generating combo boxes based on other form selections, etc. Sometimes miss those halcyon days. Access provided a quick and dirty framework for prototyping which is what we did in this case but the prototype has somehow lasted us 5 years now.

[rusnak]

FM has moved way beyond those days. I'm thinking about how to quickly calculate break even on an ongoing basis for a gas station/ mini mart. I think FM for recording fuel loads (probably several times a week, even one or more per day) and then Excel for the analytical stuff.

[Shaun @ Tru6]

I know, and you can write SQL in FM these days too. But I spend too little time on design as it is, the last thing I need to do is learn a new application.

What analytics do you need in Excel that you wouldn't use a FM report for?

[rusnak]

Maybe I can make it easier and do a break even on just fuel, since fuel pricing needs to be fast-reaction. I need some easy and fast way to enter fuel prices hundreds of times in a quickly searchable and re-sortable format, so FM is the way to go. I need to enter trucking cost per load, price paid, gallons delivered and of what grade, etc. I was thinking at the time about total company break even, but that's not necessary now that I think of it.

[Shaun @ Tru6]

Sounds interesting. What decisions will be made on the reporting? What will the results effect?

[rusnak]

I need to calculate my minimum gas pricing. I suspect that the result that I come up with will affect many people, and is the subject of some conjecture.

[Shaun @ Tru6]

Good luck!