Forum Discussion
Help needed to build a formula
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.
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.