Multiple criteria condition forumla

L

Li_zzza

New Member
Joined
Oct 3, 2017
Messages
13
Hi everyone,

I'm hoping someone here can help me (after spending hours of researching, I cannot find the solution to this).

I am working on a project around tracking certain items being sent out to certain providers and monitoring if we get anything back from the providers. However, the items are being sent out to multiple providers and sometimes one provider. I need a formula or a condition that calculates if everything is returned by the providers or provider it will say "complete" under the received all column.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Provider 1[/TD]
[TD]Provider 2[/TD]
[TD]Provider 3[/TD]
[TD]Received all?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sock[/TD]
[TD]Received[/TD]
[TD]Sent[/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Shirts[/TD]
[TD][/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Computers[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Sent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Laptops[/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Shoes[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Currently, I have the formula in E2 as =If(and(B2="Received",D2="Received"),"Complete","") but it disregards cell C2. Is there a way to add C2 into the formula, but disregard if it is empty or says the word sent?
 
Sort by date Sort by votes
Yes it does, but I was hoping it will ignore any text in between as well. In between the dates, there are some comments for notes for the product.

Shoes Received 23/10/2017 Good Sent 4/10/2017 Received 25/10/2017...etc.

I appreciate all of the help & sorry for making this complicated.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Unless you explain your set up exactly, without ability to see your PC monitor, it's just repeated guesswork at what you need when you're not providing the full picture.

Example, you're showing the stuff above with "comments for notes for the product" - what cells is this data in? Did you include this in your initial example, if not why not?

Excel assumes data types, based on the data entered into cells. You can have types like date, numbers, strings, boolean and they can all be processed and evaluated differently within formula.

But without your problem properly explained with sufficient but succinct detail, such that a blind person, unable to see your screen, can still understand your problem and provide solution, it's going to be lots of trial and errors as you drip feed information about your set up. Just delays getting to a working solution I'm afraid.
 
Upvote 0
Li_zzza said:
Yes it does, but I was hoping it will ignore any text in between as well. In between the dates, there are some comments for notes for the product.

Shoes Received 23/10/2017 Good Sent 4/10/2017 Received 25/10/2017...etc.

I appreciate all of the help & sorry for making this complicated.
Click to expand...


Hi
Is your problem spelling ?
You seem to have received and recieved in your sample data.

Regards
Murph123
 
Upvote 0
Thanks @Murphy123, I noticed the change from Received to Recieved but forgot to mention to check that too, hopefully that fixes the problem!
 
Last edited:
Upvote 0
Thank you for replying so quickly, however, no, I have checked and it's not a problem with the spelling. The sample data I gave is essentially a smaller version of a complicated workbook. Essentially, we are trying to keep track of what is being sent out and what has come back, however, there can be one or multiple providers for each item.

So for example, there are 5 providers that the items can be sent to. We need to remittance from each provider that the item was sent out to, to mark as completed for that item. However, there are a number of columns in between these providers to help us keep track of dates, comments, descriptions, etc. I am looking for a formula that will tell me if we were to send the items out to x-number of providers, and once 'Sent' is changed to 'Received', that it will mark the item as 'Completed'. The formula needs to work the same where we, for example, only send the item to 1 provider instead of 5 or send the items to all 5 providers.


For example (I only created 3 providers, but imagine, this goes on for 5 providers with the same columns)
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Items[/TD]
[TD]Transaction completed? Where the formula will go[/TD]
[TD]Provider 1: Item Number[/TD]
[TD]Provider 1: Status[/TD]
[TD]Proivder 1: Date Sent[/TD]
[TD]Provider 1: Countdown date[/TD]
[TD]Proivder 1: Rejection date[/TD]
[TD]Provider 1: Comments [/TD]
[TD]Provider 2: Item number[/TD]
[TD]Provider 2: Status[/TD]
[TD]Provider 2: Date Sent[/TD]
[TD]Provider 2: Countdown Date [/TD]
[TD]Provider 2: Rejection date[/TD]
[TD]Provider 2: Comments [/TD]
[TD]Provider 3:Item Number[/TD]
[TD]Provider 3: Status[/TD]
[TD]Provider 3: Date sent[/TD]
[/TR]
[TR]
[TD]Pens[/TD]
[TD][/TD]
[TD]1454531[/TD]
[TD]Sent[/TD]
[TD]24/10/2017[/TD]
[TD]11/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Recieved [/TD]
[TD]09/10/2017[/TD]
[TD]29/09/2017[/TD]
[TD][/TD]
[TD]Still waiting on provider 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD]Completed[/TD]
[TD]415451
[/TD]
[TD]Recieved [/TD]
[TD]09/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipping complication[/TD]
[TD]446515[/TD]
[TD]Received [/TD]
[TD]11/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1541
3102[/TD]
[TD]Recieved [/TD]
[TD]09/08/2017[/TD]
[/TR]
[TR]
[TD]Shirts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1632320[/TD]
[TD]Sent[/TD]
[TD]11/06/2017[/TD]
[TD]08/08/2017[/TD]
[TD]09/08/2017[/TD]
[TD]Rejected - Need to action[/TD]
[TD]112323[/TD]
[TD]Recieved[/TD]
[TD]08/08/2017[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Li_zzza said:
Thank you for replying so quickly, however, no, I have checked and it's not a problem with the spelling. The sample data I gave is essentially a smaller version of a complicated workbook. Essentially, we are trying to keep track of what is being sent out and what has come back, however, there can be one or multiple providers for each item.

So for example, there are 5 providers that the items can be sent to. We need to remittance from each provider that the item was sent out to, to mark as completed for that item. However, there are a number of columns in between these providers to help us keep track of dates, comments, descriptions, etc. I am looking for a formula that will tell me if we were to send the items out to x-number of providers, and once 'Sent' is changed to 'Received', that it will mark the item as 'Completed'. The formula needs to work the same where we, for example, only send the item to 1 provider instead of 5 or send the items to all 5 providers.


For example (I only created 3 providers, but imagine, this goes on for 5 providers with the same columns)
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Items[/TD]
[TD]Transaction completed? Where the formula will go[/TD]
[TD]Provider 1: Item Number[/TD]
[TD]Provider 1: Status[/TD]
[TD]Proivder 1: Date Sent[/TD]
[TD]Provider 1: Countdown date[/TD]
[TD]Proivder 1: Rejection date[/TD]
[TD]Provider 1: Comments[/TD]
[TD]Provider 2: Item number[/TD]
[TD]Provider 2: Status[/TD]
[TD]Provider 2: Date Sent[/TD]
[TD]Provider 2: Countdown Date[/TD]
[TD]Provider 2: Rejection date[/TD]
[TD]Provider 2: Comments[/TD]
[TD]Provider 3:Item Number[/TD]
[TD]Provider 3: Status[/TD]
[TD]Provider 3: Date sent[/TD]
[/TR]
[TR]
[TD]Pens[/TD]
[TD][/TD]
[TD]1454531[/TD]
[TD]Sent[/TD]
[TD]24/10/2017[/TD]
[TD]11/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Recieved[/TD]
[TD]09/10/2017[/TD]
[TD]29/09/2017[/TD]
[TD][/TD]
[TD]Still waiting on provider 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD]Completed[/TD]
[TD]415451[/TD]
[TD]Recieved[/TD]
[TD]09/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipping complication[/TD]
[TD]446515[/TD]
[TD]Received[/TD]
[TD]11/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1541
3102[/TD]
[TD]Recieved[/TD]
[TD]09/08/2017[/TD]
[/TR]
[TR]
[TD]Shirts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1632320[/TD]
[TD]Sent[/TD]
[TD]11/06/2017[/TD]
[TD]08/08/2017[/TD]
[TD]09/08/2017[/TD]
[TD]Rejected - Need to action[/TD]
[TD]112323[/TD]
[TD]Recieved[/TD]
[TD]08/08/2017[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi
Is there anywhere you record the number of providers you use, does it depend on the item or does it vary on some other criteria if you could insert a column to record it.

Regards
Murphy123
 
Upvote 0
@JackDanIce There are always 11 columns in between the providers
@Murphy123 The item get sent to what kind of provider will accept them. Sometimes more than 1 provider will accept the item, therefore we will send it to both places. We have in total 5 providers but over hundreds of items
 
Upvote 0
Li_zzza said:
@JackDanIce There are always 11 columns in between the providers
@Murphy123 The item get sent to what kind of provider will accept them. Sometimes more than 1 provider will accept the item, therefore we will send it to both places. We have in total 5 providers but over hundreds of items
Click to expand...

Hi

I assumed 11 columns per provider so if it is 12 per provider then you will have to extend the ranges(red text)
put the formula in cell B2 and copy down

<colgroup width="162"></colgroup> <tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: left"] =IF(AND(COUNTIF(C2:BE2,"Sent")=0,COUNTIF(C2:BE2,"Received")>0),"Complete","")

You can have Sent or Received elsewhere on the row of data other than the status cells
but they must have at least one other character in the cell, they must be strictly Sent or Received in the status cells.

Regards
Murphy123
[/TD]

</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
How to make this IF formula work?
Replies
1
Views
252
Fluff
Fluff
K
  • Question Question
Return Value Based on Multiple Criteria
Replies
3
Views
946
KMorrison12345
K
P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
3K
Plod518
P
D
  • Solved
Overriding Conditional Formatting Color with Manual Entries of a different color
2
Replies
15
Views
4K
Joe4
Joe4
S
  • Question Question
is there a formula that can return multiple matches to criteria and put each match in its own cell
Replies
2
Views
668
supermom28115
S

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top