Forum Discussion
Help needed to build a formula
In a excel wb, I have three tabs, Sheet 1, Summary, and Office supplies. I want to create a logic in Summary tab, where it checks in the entire range (A2:A16) in Sheet 1 for any UCN matches to column B:B in Office supplies tab, if one match is found, it should flag as “Office supply” but by default when the Sheet 1 is blank the section 4 in summary tab should be blank
Here’s the formula I created,
=IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A16,'Office supplies'!B:B,0))) > 0,"Office Supply","Not an Office Supply")
This works fine the only challenge is, by default when the Sheet 1 blank it shows as “Not an Office supply”, instead I want that to be blank-
I also tried adding ISBlank to it but excel gives spill error:
=IF(ISBLANK(Sheet1!A2:A16), “”,IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A16,'Office supplies'!B:B,0))) > 0,"Office Supply","Not an Office Supply")
To achieve the desired behavior where the section 4 in the Summary tab remains blank when the Sheet 1 is blank, you can use the following modified formula:
=IF(COUNTA(Sheet1!A2:A16)=0, "", IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A16, 'Office supplies'!B:B, 0))) > 0, "Office Supply", "Not an Office Supply"))
This formula uses the COUNTA function to check if there are any non-blank cells in the range A2:A16 of Sheet 1. If the count is zero (indicating that the range is blank), it returns a blank result. Otherwise, it proceeds with your existing logic to determine if the UCN matches any entries in the Office supplies tab. The text was created with the help of AI.
Without having looked at your file, for personal security reasons and based on the text, I am sending you this suggested solution or approach.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
1 Reply
To achieve the desired behavior where the section 4 in the Summary tab remains blank when the Sheet 1 is blank, you can use the following modified formula:
=IF(COUNTA(Sheet1!A2:A16)=0, "", IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A16, 'Office supplies'!B:B, 0))) > 0, "Office Supply", "Not an Office Supply"))
This formula uses the COUNTA function to check if there are any non-blank cells in the range A2:A16 of Sheet 1. If the count is zero (indicating that the range is blank), it returns a blank result. Otherwise, it proceeds with your existing logic to determine if the UCN matches any entries in the Office supplies tab. The text was created with the help of AI.
Without having looked at your file, for personal security reasons and based on the text, I am sending you this suggested solution or approach.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.