请求编写Excel链接公式：NEW工作表23-28行公式待优化

Hey there! Let's work through those formula snags in your NEW worksheet's rows 23-28. Since you already have a solid base with the SUMPRODUCT(--ISNUMBER(SEARCH)) conditional counting logic, we can adapt that directly to fit the second tab's needs.

First, a quick recap of your core working logic—this combo is perfect for partial matches or multi-criteria counting:

=SUMPRODUCT(--ISNUMBER(SEARCH("criteria", target_range)))

Tailored Fixes for NEW Worksheet Rows 23-28 #

Here’s how to adjust this formula specifically for your second tab, keeping in mind worksheet letters can’t be modified:

1. Double-Check Cross-Sheet References
   If your source data lives in the first worksheet (let's call it Original for example), make sure you’re referencing it correctly. For instance, if row 23 needs to count matches between NEW!B23 and Original!A:A, use:

   =SUMPRODUCT(--ISNUMBER(SEARCH(NEW!B23, Original!A:A)))
   

   Pro tip: Skip full-column references like A:A when you can—use a specific range (e.g., Original!A2:A1000) to make calculations faster.

2. Handle Multiple Criteria (If Required)
   If rows 23-28 need to count against OR conditions (e.g., match "X" or "Y"), extend the formula with an array of criteria:

   =SUMPRODUCT(--(ISNUMBER(SEARCH({"criteria1","criteria2"}, Original!A:A))))
   

   For AND conditions (match "X" and "Y"), nest two ISNUMBER(SEARCH) checks:

   =SUMPRODUCT(--ISNUMBER(SEARCH("criteria1", Original!A:A)) * --ISNUMBER(SEARCH("criteria2", Original!A:A)))
   

3. Lock Ranges for Easy Dragging
   When dragging the formula down rows 23-28, lock your source data range with dollar signs so it doesn’t shift. For example:

   =SUMPRODUCT(--ISNUMBER(SEARCH(B23, Original!$A$2:$A$1000)))
   

   This keeps Original!$A$2:$A$1000 fixed while B23 updates automatically as you drag down.

4. Fix Common Error Causes

   • If you see #VALUE! errors, clean up extra spaces in your criteria with TRIM():

     =SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(B23), Original!$A$2:$A$1000)))
     

   • If counts are incorrect, remember SEARCH is case-insensitive—swap it for FIND if you need case-sensitive matches.

Example for NEW!Row 23 #

Let’s say row 23 needs to count partial matches between NEW!C23 and Original!D:D:

=SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(NEW!C23), Original!$D$2:$D$1500)))

Just plug in your actual column letters and ranges from your file, and these formulas should work seamlessly for rows 23-28 in the NEW tab.

内容的提问来源于stack exchange，提问作者Zhang