Hi! Thanks in advance for any help. I'm trying to automate part of the expense report I use. I enjoying playing around with formulas, but am a novice at best and don't know how to proceed here.



A little background:

-the expense report is biweekly, and the same report is used for both the first and second part of every month. This means that each date row has two values (1st & 16th, 10 & 25th, 15th & 30th, etc.). The dates are always mutually exclusive (there will never be an expense report with both the 1st and the 16th on it).

-data is entered on the second page and transferred onto the first page for most sections automatically.

In the expense report, there is a section that does not automatically tabulate and transfer the amount to the first page because the formula to do so is much more complicated than the rest.

This section is below:



Using the example above, I am interested in developing a formula to do something like the following, crudely put:

(=IF (DAY((A23))=1 OR 16 AND IF C23="O" OR "P/T" THEN B23, 0) + (=IF (DAY((A24))=1 OR 16 AND IF C24="O OR "P/T" THEN B24, 0) + (=IF (DAY(A25))=1 or 16 AND IF C25="O" OR "P/T" THEN B25, 0) ... ((=IF (DAY((A38))=1 OR 16 AND IF C38="O OR "P/T" THEN B38, 0)

In other words, I would want to know what 2.50+2.20+6.00 is because they are

-all incurred on the same date, and
-are either "P/T" designated (as in the case of the first two) or "O" designated.

Whatever the sum proves to be, I would then want it to be transferred to the first page by the corresponding date and in the appropriate column (in this case the 1st of the month under "parking/tolls and other":



(note how each row has two dates assigned to it to save space)

Some problems include:

I never know how many receipts I am going to have for any particular day.

The second page section is set up in such a way that there is not a permanent cell to cell ratio. As in the example above, I may have three receipts on the 1st, no receipts on the 2nd, and one receipt on the 3rd. I can't always link A26 to the 3rd, for example, since I may have no receipts for the 1st and 2nd on next month's sheet.

I therefore need to write a formula that will evaluate every row in the section for meeting two conditions. Every row that meet the criteria for a particular day (and designation, "O" or "P/T") would then be added up and displayed by the corresponding date on the first page. Ideally I'd like to use the same for Entertainment ("E") costs, but I almost never have those. Yet I *often* have receipts!

(note: I manually entered these values onto the first page by way of example. Having to manually enter them, though, is precisely what I want to avoid)!

Does that make sense?

I have attached the spreadsheet for reference if anyone would care to play with it. I realize this may be ridiculously simple for some, but any advice whatsoever would be greatly appreciated!

-CrabApple

Attached Files

Expense Report (blank).xls (70.0 KB, 14 views)

Hello,

I hope I've understood your question and please excuse me because I'm French so I don't speak very well ...

May be with this formula in cell M12:

In english I think it is:

Hope it will help you.

m

Thank you for your response, MTH. I am working with your formula now. In the meantime, I devised a much less elegant formula that seems to accomplish what I want.

For the "P/T" and "O," it looks like the following (for the 8th or 23rd of the month):

=(IF(AND(OR(DAY(('Expense Report Page 2'!A23))=8,DAY(('Expense Report Page 2'!A23))=23),OR('Expense Report Page 2'!C23="P/T",'Expense Report Page 2'!C23="O")),'Expense Report Page 2'!B23,0))+(IF(AND(OR(DAY(('Expense Report Page 2'!A24))=8,DAY(('Expense Report Page 2'!A24))=23),OR('Expense Report Page 2'!C24="P/T",'Expense Report Page 2'!C24="O")),'Expense Report Page 2'!B24,0))+(IF(AND(OR(DAY(('Expense Report Page 2'!A25))=8,DAY(('Expense Report Page 2'!A25))=23),OR('Expense Report Page 2'!C25="P/T",'Expense Report Page 2'!C25="O")),'Expense Report Page 2'!B25,0))+ (...) and so on, for all of the rows in question.

I don't mind using this one, but yours looks much simpler and I look forward to giving it a whirl.

Thanks again!

-CA