CountIFs with Index and Date

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

I'm trying to figure out a formula that shows if the date changes on a specific index it would result in a rejection status.
So i'm trying to group The index with the date received and if the table shows anything that's different from the date received date it's considered a rejection.
What's the best way to do this?
I thought a countifs with an IF statement would work but it shows everything as rejected for some reason.
We make trackers twice a day so if it carries over technically it doesn't mean it's been rejected, but if the date does change when we received it it does mean it was rejected.

Here is a sample
Source.NameFormindexAnalystTask TypeMaster Data ProcessMD Rec'dPriorityStatus
AM - MD OTR 11.16.20.xlsx
136074​
ARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.20
11/12/2020​
PriorityRejected
AM - MD OTR 11.17.20.xlsx
136074​
mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.20
11/13/2020​
PriorityRejected
AM - MD OTR 11.18.20v.xlsx
136074​
mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.20
11/13/2020​
PriorityRejected
PM - MD OTR 11.16.20.xlsx
136074​
mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.20
11/12/2020​
PriorityRejected
PM - MD OTR 11.17.20.xlsx
136074​
mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.20
11/13/2020​
PriorityRejected
PM - MD OTR 11.18.20.xlsx
136074​
mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.20
11/13/2020​
PriorityRejected

Since the date changed from 11/12/2020 to 11/13/2020, it's an indication that the request has been rejected.

Would removing duplicates by the selection of formindex and mdrec'd together fix that issue?
Please help.
Thank you.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Hi Beginvbaanalyst,

You don't show the formula you tried but does this work?

Book1
ABCDEFGH
1Source.NameFormindexAnalystTask TypeMaster Data ProcessMD Rec'dPriorityStatus
2AM - MD OTR 11.16.20.xlsx136074ARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.2011/12/2020PriorityRejected
3AM - MD OTR 11.17.20.xlsx136074mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.2011/13/2020PriorityRejected
4AM - MD OTR 11.18.20v.xlsx136074mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.2011/13/2020PriorityRejected
5PM - MD OTR 11.16.20.xlsx136074mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.2011/12/2020PriorityRejected
6PM - MD OTR 11.17.20.xlsx136074mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.2011/13/2020PriorityRejected
7PM - MD OTR 11.18.20.xlsx136074mtaurasARTICLE_CHANGEARTICLE_CHANGE - Remove Simple Tire UPCS (see attached list) 11.06.2011/13/2020PriorityRejected
Sheet1
Cell Formulas
RangeFormula
H2:H7H2=IF(COUNTIF($B$2:$B$9999,B2)<>COUNTIFS($B$2:$B$9999,B2,$F$2:$F$9999,F2),"Rejected","OK")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,384
Messages
5,635,993
Members
416,891
Latest member
Okomomo

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
Top