Formula for multiple conditions testing

C

cybeh

New Member
Joined
Sep 7, 2011
Messages
3
Hi All, am new to this forum and this is my first post, though I have been visting it for last many years seeking help.

I am looking to create a formula which tests and vaidates multiple conditions and reutrn the count after that. I'll explain the requirement below:

The dummy data will look like this:

<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=330 border=0><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=96 height=21>System Name </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 80pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=106>Date of Closure </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Category </TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Status</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Windows</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">30-Jul-11</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">High</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Open</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Unix</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">30-Sep-11</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Medium</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Open</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Network</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">31-Jan-12</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Low</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Open</TD></TR></TBODY></TABLE>

Now I need to pull in a dashboard with the above data, which looks like this:


<TABLE style="WIDTH: 641pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=855 border=0><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl85 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 59.25pt; BACKGROUND-COLOR: transparent" width=96 height=79 rowSpan=2>System </TD><TD class=xl87 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 185pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=247 colSpan=4>High</TD><TD class=xl88 dir=ltr style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 192pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=256 colSpan=4>Medium</TD><TD class=xl88 dir=ltr style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=256 colSpan=4>Low</TD></TR><TR style="HEIGHT: 43.5pt" height=58><TD class=xl82 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 43.5pt; BACKGROUND-COLOR: transparent" width=55 height=58>Due in over 30 days</TD><TD class=xl83 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Due in 30 days </TD><TD class=xl83 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Past Due less than 30 days</TD><TD class=xl108 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Past Due Beyond 30 days </TD><TD class=xl82 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Due in over 30 days</TD><TD class=xl83 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Due in 30 days </TD><TD class=xl83 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Past Due less than 30 days</TD><TD class=xl84 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Past Due Beyond 30 days </TD><TD class=xl109 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Due in over 30 days</TD><TD class=xl83 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Due in 30 days </TD><TD class=xl83 dir=ltr style="BORDER-RIGHT: #be4b48 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Past Due less than 30 days</TD><TD class=xl84 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #be4b48; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>Past Due Beyond 30 days </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=96 height=20>Windows</TD><TD class=xl101 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=55></TD><TD class=xl102 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl102 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl103 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl104 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl105 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl105 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl106 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl75 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl74 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl74 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl107 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl79 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=96 height=20>UNIX</TD><TD class=xl80 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=55></TD><TD class=xl71 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl71 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl81 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl77 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl72 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl72 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl78 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl90 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl91 dir=ltr style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=96 height=21>Network</TD><TD class=xl92 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=55></TD><TD class=xl93 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl93 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl94 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl95 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl96 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl96 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl97 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #f0d5d4" width=64></TD><TD class=xl98 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl99 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl99 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl100 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64></TD></TR></TBODY></TABLE>


Now we have to put in a formula in each cell so if the statement holds true it should populate the count else stays blank. I worked out the logical steps and stuck at the last condition as number of days will be returned as negative for dates after the current date.

Code: 
4 conditions
 
a. System Name
b. Criticality (High-Medium-Low)
c. status (open-closed)
d. Date or Number of Days
 
Step 1: Search for the System name
Step 2: Search for the Crtitcality
Step 3: Search for the Status (Open-Closed)
Step 4: Search for the number of days pending from the mentioned Date

Let me know how can I achieve the above goal, using countifs formula and the last condition. Thanks in advance.

Cheers,
Cybeh
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Meaning for the dashboard or the dummy data?

I assume its the dashboard.
  • Due in 30 days means= It will be actioned or due for action in coming 30 days
  • Due in Over 30 days means= It will be actioned in more than 30 days
  • Past due in 30 days means= It was not actioned and is pending but less than 30 days
  • Past due in over 30 days means= It was not actioned and is now pending for more than 30 days.
Hope this helps.

Cheers,
 
Upvote 0
  • Due in 30 days means= It will be actioned or due for action in coming 30 days (Means <30) and what is the meaning of actioned and how it is related to u r data)
  • Due in Over 30 days means= It will be actioned in more than 30 days Means <=30) and what is the meaning of actioned and how it is related to u r data)
  • Past due in 30 days means= It was not actioned and is pending but less than 30 days
  • Past due in over 30 days means= It was not actioned and is now pending for more than 30 days.
see main thing i want to ask is. How u r action word related to your data.
 
Upvote 0
Yeah that's right.

Due in 30 days means <30, but this 30 will be a negative value as we have to subtract the future date from the current date.

For eg. in the dummy data the 4th row item (network) is of Low category and due in over 30 days (that is next year) so value 1 should come up in the dashboard Under Low (Due in over 30 days) against Network

Cybeh
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
853
gvsumich
G
P
  • Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
245
John_w
J
E
  • Question
VBA for Web Scraping help
Replies
0
Views
606
epactheactor
E
E
  • Question
Adding a custom message.
Replies
1
Views
446
Micron
M

Forum statistics

Threads
1,215,480
Messages
6,125,048
Members
449,206
Latest member
Healthydogs

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