Something like SUMPRODUCT but outputting text?

hugoexcel22

New Member
Joined
May 31, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Good morning,

I'm making a report with a summary sheet where the data must be displayed, I already made 1 column that works perfectly fine and other one that is not working properly.

I have this formula:

=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")

For other cells I would have to add this also:

=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$C:$C;C2;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")

I also would need it to read not only the "CRITICAL" but also if it contains MEDIUM-HIGH, MEDIUM, LOW and IG, but I don't know how to add that (is not even working now so...)

That returns a "SPILL", what I need is the following:

PCI PENDING GAPS.xlsx
ABCDE
1THING 1THING 2ASSETSERVICECURRENT STATUS
2THING ATHING A2--#SPILL!
3THING BTHING B2ASSET1SERVICE1#SPILL!
Sheet1
Cell Formulas
RangeFormula
E2E2=IF(ISNUMBER(MATCH(VULNERABILITIES!$A:$A,STATUS!A2,0))*ISNUMBER(MATCH(VULNERABILITIES!$B:$B,STATUS!B2,0))*ISNUMBER(MATCH(VULNERABILITIES!L:L,{"CRITICAL"},0)),{"CRITICAL"},"NOT FOUND")
E3E3=IF(ISNUMBER(MATCH(VULNERABILITIES!$A:$A,A3,0))*ISNUMBER(MATCH(VULNERABILITIES!$B:$B,B3,0))*ISNUMBER(MATCH(VULNERABILITIES!L:L,{"CRITICAL"},0)),{"CRITICAL"},"NOT FOUND")
Named Ranges
NameRefers ToCells
VULNERABILITIES!_FilterDatabase=VULNERABILITIES!$A$1:$N$190E2:E3


This is sheet 1 where I have to put the formula because it is the summary, and this is sheet 2 where the data is taken:

PCI PENDING GAPS.xlsx
ABCDEF
1THING 1THING 2ASSETSERVICESEVERITYSTATUS
2THING ATHING A2--CRITICALUNRESOLVED
3THING BTHING B2ASSET1SERVICE1MEDIUM-HIGHUNRESOLVED
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F1:F3Cell Valuecontains "UNRESOLVED"textNO
F1:F3Cell Valuecontains "PENDING"textNO
F1:F3Cell Valuecontains "PPVV"textNO
F1:F3Cell Valuecontains "SOLVED"textNO
E1:E3Cell Valuecontains "CRITICAL"textNO
E1:E3Cell Valuecontains "MEDIUM-HIGH"textNO
E1:E3Cell Valuecontains "MEDIUM"textNO
E1:E3Cell Valuecontains "LOW"textNO
E1:E3Cell Valuecontains "IG"textNO
Cells with Data Validation
CellAllowCriteria
E2:E3ListCRITICAL;MEDIUM-HIGH;MEDIUM;LOW;IG
F2:F3ListUNRESOLVED;SOLVED;PPVV;PENDING


Basically I need to show on Sheet1 the summary of the data from Sheet2, I have 2 main values: Thing A and Thing B, but Thing A can be linked to Thing B or C, the status of the Thing can be 4: Unresolved, Resolved, PPVV and Pending, which also can have 5 possible severity results CRITICAL, MEDIUM-HIGH, MEDIUM, LOW, IG. What I need to show on Sheet1 is the most serious, I have used the following formulas for the other column that does not appear and it works perfectly:

=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))

=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!C:C;C3;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))

What I need is something similar but output the specified text based on existing unresolved rows.

If I'm not being clear please tell me.

Regards.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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