If statement assistance!!

Hello,
I am working on an invoice tracking sheet. My main point of difficulty is that I am trying to reference a multiple cells and return one specific outcome. Here are the details:

I have a number of expenditure groups (ie: catering, travel, equipment). I would like to track multiple invoices based on their invoice status (received, pending, paid).

How do I create a formula that looks at the invoice status for multiple invoices in similar expenditure groups and return a text value?

for example: I have 3 catering invoices where 2 are paid and 1 is still pending. I would like to return in the cell "Invoice Pending".

I thought of giving each invoice status a value. so for received=1, pending =2 paid =3. that way i would always reference the lowest value, but return the text value associated with the invoice status.

I am having a difficult time building a formula that can do this.

Any suggestions?

I have attached the workbook that I have been using to help with this situation

All expenditures are tracked on tab 2 TMB expenditure tracking. This will list all of the information from the transaction (type, price, date, invoice status, etc.) Each expenditure will be given a status to track. I would like to collect all the invoices that are grouped by a Expenditure Group (Catering, Mail, Travel, Supplies & Equipment, Vendor & Consulting - Within List Data tab) and track their invoice status (Invoice Received, Invoice Approved, Invoice sent to Jennifer, Invoice sent to IFIS, Invoice Paid - Within List Data Tab).

My goal is to list 1 invoice status returned on the course overview page under Invoice Status based on the combined statuses of each invoice. For example all of the invoices associated in the catering group have different status, 2 - invoice paid and 1 invoice approved. I would like to return invoice approved because it has yet to finish the cycle

To be honest ryan, I find it hard to follow exactly what you're trying to accomplish here.

Although you provided a sample, but you don't really have a 'desired' solution and how to derive it. So it's difficult to follow what you're explaining and the desired solution that you'd like to achieve.

Hi Ryan,

I am trying to understand what you are trying to achieve on here, but If I understand it as follows: (I'll use catering as the example)

1) You are looking at catering for Quarter 1
2) If it says anything other than Invoice-Paid then it will be Invoice Pending?

I would do a 4 columns for each quarter representing each service.
Use a countifs statement to total the amount of invoice that does not equal "Invoice Approved". Do an If statement if that is more than >0 then to "Invoice Pending", otherwise "Invoice for Catering Service Paid for Q1".

Hope this helps.

Hi,

i get a number of invoices, that can be categorized into different groups (catering, travel, etc.) those invoices come from different vendors. In my tracking sheet I give each invoice a status (received, pending, paid, etc)

I would like to show the status for the "group". Meaning, based on the invoices received for catering, if all invoices have a status of paid, then in the first tab for catering it would come back invoice paid.

But if 1 of the invoices comes back with different statuses, then I would like it to show the lowest value which would be invoice received. Invoice paid would be the desired (highest) value as the invoice has now been dealt with.

This will provide my manager with a high level picture that we still need to look at our invoices for catering because 1 is still outstanding or not paid.

I would like this for multiple groups for different course sessions.

hopefully this clears it up.