Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-08-06· OpenAIAi2 WildChat-4.8M (AI2)

google sheet lookup 2 condittion both row and column

  1. User

    google sheet lookup 2 condittion both row and column

  2. Assistant

    To perform a lookup in Google Sheets with both row and column conditions, you can use a combination of functions like FILTER, INDEX, MATCH, or ARRAYFORMULA. One common approach is to use the FILTER function along with conditions that match both the row and the column criteria. Here’s a step-by-step guide to achieve this:

    Assume you have a table of data where you want to find a value based on a row and column condition. For example:

          | A        | B        | C 
    --|----------|----------|----------
    1 |          | Header1  | Header2  
    2 | Row1     | Value1-1 | Value1-2 
    3 | Row2     | Value2-1 | Value2-2 
    4 | Row3     | Value3-1 | Value3-2 
    

    Let's say you want to find the value where the row condition is Row2 and the column condition is Header2.

    Using FILTER Function

    1. Define Your Row and Column Headers:

      • Row condition: Row2
      • Column condition: Header2
    2. Use FILTER along with conditions:

      =FILTER(B2:C4, A2:A4 = "Row2", B1:C1 = "Header2")
      

      In this formula:

      • B2:C4 is the range of data you are considering.
      • A2:A4 = "Row2" filters the rows to match Row2.
      • B1:C1 = "Header2" filters the columns to match Header2.

    Using INDEX and MATCH Functions

    This method gives the same result but is often used for exact cell location retrieval using index positions.

    1. Prepare your conditions:

      • Row condition (match): Find the position of Row2 in the row headers.
      • Column condition (match): Find the position of Header2 in the column headers.
    2. Apply the INDEX and MATCH functions:

      =INDEX(B2:C4, MATCH("Row2", A2:A4, 0), MATCH("Header2", B1:C1, 0))
      

      In this formula:

      • INDEX(B2:C4, ...) specifies the range from which to retrieve data.
      • MATCH("Row2", A2:A4, 0) finds the row number where Row2 is located.
      • MATCH("Header2", B1:C1, 0) finds the column number where Header2 is located.

    Explanation:

    • FILTER directly gives you the result by evaluating the array with conditions.
    • INDEX and MATCH are more about position location and retrieval, which can be powerful if integrating into more complex formulas.

    Additional Note:

    Ensure that the conditions (such as Row2 and Header2) match exactly the text in your sheet, as these functions are case-sensitive and include whitespace.

  3. User

    17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 11182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11136 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11190 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 31 0 0 11144 0 0 5 0 1 0 0 0 0 6 0 0 0 0 2 0 0 0 0 0 18 0 1 11101 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11174 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11210 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11192 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

  4. Assistant

    To perform a lookup in your dataset with both row and column conditions in Google Sheets, you can use functions like INDEX and MATCH. Here's a guide based on your data:

    Assuming the layout of your data starts from cell A1 as follows:

         |    A   |    B   |    C   |    ...   |    X   |
    1    |  17.3  |  17.3  |  17.3  |    ...   |  17.3  |
    2    | 11182  |   0    |   0    |    ...   |   0    |
    3    | 11136  |   0    |   0    |    ...   |   0    |
    4    | 11190  |   0    |   0    |    ...   |  31    |
    5    | 11144  |   0    |   0    |    ...   |   1    |
    ...
    

    If you want to find a value based on a specific ID (in column A) and a specific header value (e.g., '17.3'), follow these steps:

    Using INDEX and MATCH

    1. Identify Your Conditions:

      • Let's say you want to find the value in the row where ID = 11190 and the column with a header 17.3.
    2. Apply the Formula:

      1. Row with MATCH: Find the position of 11190 in column A.
      2. Column with MATCH: Find the position of the first occurrence of 17.3 in row 1.

      Your formula will be like this:

      =INDEX(A2:X, MATCH(11190, A2:A, 0), MATCH(17.3, A1:X1, 0))
      
      • INDEX(A2:X, ...) specifies the range to search for the value.
      • MATCH(11190, A2:A, 0) finds the row number where 11190 is located in column A.
      • MATCH(17.3, A1:X1, 0) finds the first column number where 17.3 is found in row 1.

    Explanation:

    • MATCH(11190, A2:A, 0) returns the relative row index starting from row 2, so it returns 3 because 11190 is in the 3rd row of the data range starting from row 2.
    • MATCH(17.3, A1:X1, 0) returns the column index of the first 17.3 found in the header row.

    This will give you the value in the intersection of the row where 11190 is and the column where 17.3 is found.

    Note:

    • Make sure headers like 17.3 occur only once or handle multiple instances as needed.
    • Adjust the range A2:X to match the actual data range in your sheet.
    • The setup assumes no duplicate IDs.
  5. User

    ต้องการผลรวมของ แต่ละ store id (column A) ว่า มีราคาเท่ากับ header จำนวนเท่าไร

  6. Assistant

    To calculate the sum of occurrences where each store ID in column A corresponds to the price in the header row (row 1), you can use Google Sheets formulas such as SUMPRODUCT combined with logical comparisons. Here’s how you can approach this problem:

    Scenario:

    You have store IDs in column A and price values in row 1, and you want to know how many times each store ID has values that are equal to the prices specified in the headers.

    Step-by-Step Guide:

    Let's assume your data range is A1:X8 based on the number of columns in your example. Follow these steps to get the count for each ID:

    1. Add Output Column:

      • Choose a column to store your result, say starting from column Y for each store ID.
    2. Apply the Formula:

      • In cell Y2, write the following formula and then drag it down for each store ID:
      =SUMPRODUCT(--(B$1:X$1 = 17.3), --(B2:X2 = B$1:X$1))
      

      Explanation of the Formula:

      • B$1:X$1 = 17.3: This checks whether the price in each column of the header row equals 17.3.
      • B2:X2 = B$1:X$1: This checks whether the values in the row for the store ID match the header prices.
      • --(condition): This double negative converts the TRUE/FALSE resulting from the logical checks into 1/0 respectively.
      • SUMPRODUCT(...): Calculates the sum of products, which is effectively counting matches based on logic conditions.
    3. Adjust the Reference If Needed:

      • Ensure that your range (B1:X1) correctly includes all relevant columns and that the header row correctly identifies your price points.
    4. Copy Down:

      • Copy the formula in cell Y2 down to other rows in column Y to get the count for each store ID.

    By following this approach, you'll be able to see how many times the header prices occur in the row for each store ID, and you’ll have a column dedicated to this count. Adjust the header price (e.g., 17.3) to suit your specific requirement if you want to match different price points.

  7. User

    store/price 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 11182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11136 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11190 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 31 0 0 0 11144 0 0 5 0 1 0 0 0 0 6 0 0 0 0 2 0 0 0 0 0 18 0 1 0 11101 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11174 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11210 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11192 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

  8. Assistant

    To calculate how many times each store ID has a value that matches the header value (17.3) in your dataset, you need to count the occurrences in each row where the stored value matches the header value.

    Here's how you can accomplish this in Google Sheets:

    Step-by-Step Guide

    1. Select an Output Column:

      • Let's say you want to place the results in column Y, starting from row 2.
    2. Write the Formula:

      • In cell Y2, input the following formula:
      =SUMPRODUCT(--(B2:Y2 = B$1:Y$1))
      

      Explanation:

      • B$1:Y$1 corresponds to the header row which contains 17.3. Adjust Y in your formula to match the end of your column range.
      • B2:Y2 = B$1:Y$1: This compares each element in the row for the store with the corresponding header value.
      • --(condition): This converts TRUE/FALSE results to 1/0, allowing you to sum these with SUMPRODUCT.
    3. Drag the Formula Down:

      • Drag the formula from cell Y2 down to cover all the rows for your store IDs (e.g., Y2:Y9 in this case).

    By following these steps, you'll obtain the count of occurrences where each store's value matches the header value of 17.3.

    Full Formula to Copy Down

    =SUMPRODUCT(--(B2:Y2 = B$1:Y$1))
    
    • Ensure your ranges (B2:Y2 and B$1:Y$1) match the actual cell positions in your dataset.
    • The range Y2 presumes you're using column Y to place output results; adjust accordingly to your sheet layout.

    This method will allow you to automatically compute how many times the value for each store ID equals 17.3, column by column. Adjust the formula as needed if your header value or conditions change.

  9. User

    store/price 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 15 17.3 17.3 17.3 17.3 17.3 20 17.3 25 11182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11136 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11190 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 31 0 0 11144 0 0 5 0 1 0 0 0 0 6 0 0 0 0 2 0 0 0 0 0 18 0 1 11101 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11174 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11210 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11192 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

  10. Assistant

    Given your updated data, you want to determine the number of times each store ID has a price that matches its corresponding header value in row 1. Here's how you can accomplish this task using a formula in Google Sheets:

    Step-by-Step Guide

    For each store, you need a formula that counts the number of times the value in a row matches the header price above it.

    1. Choose an Output Column:

      • Let's assume you want to start placing the results in column Y, beginning from row 2.
    2. Formula Setup:

      In cell Y2, enter the following formula:

      =SUMPRODUCT(--(B2:X2 = $B$1:$X$1))
      
      • Explanation:
        • B2:X2 is the range for the values of the current store row.
        • $B$1:$X$1 is the range for the header containing prices. The dollar signs ($) before the row numbers ensure that the header reference stays fixed as you drag the formula down.
        • --(B2:X2 = $B$1:$X$1) converts the comparison to 1 for matches and 0 otherwise.
        • SUMPRODUCT sums these values to count matches.
    3. Expand the Formula:

      Drag the formula down column Y to apply it to all rows containing store IDs. This will give the count of prices matching the corresponding header for each store.

    Example Formula

    In your case, you would have:

    • In cell Y2 for Store ID 11182, the formula looks like:
      =SUMPRODUCT(--(B2:X2 = $B$1:$X$1))
      

    Important Adjustments:

    • Ensure that your range in the formula (B2:X2 and $B$1:$X$1) correctly matches your data range from the second row of data to the last column used.
    • You may need to replace X in the range with the actual end column based on your data. Here, it stops at column X, covering all header values.

    By following this process, each cell in column Y will display the number of times values in a respective store ID row match the header values directly above them.

  11. User

    store/price 17.3 17.3 17.3 17.3 15 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 17.3 15 17.3 17.3 17.3 17.3 17.3 20 17.3 25 17.3 30 11182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11136 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11190 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 31 0 0 0 0 11144 0 0 5 0 1 0 0 0 0 6 0 0 0 0 2 0 0 0 0 0 18 0 1 0 0 11101 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11174 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11210 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11192 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

  12. Assistant

    To calculate the sum of each store ID where the sales value matches the corresponding price listed in the header row, you can use the formula SUMPRODUCT in Google Sheets. The formula will process each row and column in the dataset to see where the values match the header.

    Here's how you can do this:

    Setting up the Formula

    1. Identify Your Output Column:

      • For instance, you can store the results in column Y, starting from cell Y2.
    2. Apply the Formula:

      • In cell Y2, enter the following formula:
      =SUMPRODUCT(--(B2:Y2 = $B$1:$Y$1))
      

      Explanation:

      • B2:Y2 is the range of sales data for the store ID in that row. Adjust this range to include all relevant columns from your dataset.
      • $B$1:$Y$1 references the header row, locking the row reference with $ so it remains constant while copying the formula down.
      • --(B2:Y2 = $B$1:$Y$1) converts the comparison results (from TRUE/FALSE) into 1 for a match and 0 otherwise.
      • SUMPRODUCT(...) calculates the total counts of matches for each row.
    3. Drag Down the Formula:

      • Copy the formula from Y2 down to cover all rows that contain your store data. This will give the count of matching prices for each store ID.

    Example:

    • Place the formula in Y2:
      =SUMPRODUCT(--(B2:Y2 = $B$1:$Y$1))
      
    • Drag the formula down for each store ID.

    Notes:

    • Ensure the range B2:Y2 correctly synchronizes with the number of columns you have in your dataset.
    • You should replace Y with the appropriate last column that matches your data (the example assumes column Y, but your dataset could extend further).

    This approach will compute how many times values in each store's row match the header's specified price, giving you the desired totals.

  13. User

    this is my summary table store/price 17.3 15 20 30 11182 0 11136 11190 11144 11101 11174 11210 11192

  14. Assistant

    To calculate how many times each store ID (in your summary table) recorded values matching each price category specified in the header (17.3, 15, 20, 30), you'll need to count the occurrences in each store's full data row where the value matches the header prices.

    Steps to Complete Your Summary Table

    Let’s fill in the summary table by obtaining the counts for each specified price:

    1. Define Your Data Range: This range includes the full original data with all stores and prices: let's say it goes from B2:AA9.

    2. Set Up the Formula for Each Price: In the summary table, you will use the COUNTIF function to count occurrences for each price.

    3. Apply the Formula: Start with row 2 for the first store ID.

      • For 17.3 in cell B2 of your summary table:

        =COUNTIF($B2:$AA2, 17.3)
        
      • For 15 in cell C2 of your summary table:

        =COUNTIF($B2:$AA2, 15)
        
      • For 20 in cell D2 of your summary table:

        =COUNTIF($B2:$AA2, 20)
        
      • For 30 in cell E2 of your summary table:

        =COUNTIF($B2:$AA2, 30)
        
    4. Drag the Formula Down: Copy the formula for each price across the row for every store ID.

    Explanation:

    • COUNTIF Function: It counts the number of cells within a range that meet the given single condition (e.g., 17.3).
    • $B2:$AA2: Ensure this range includes all data columns applicable for counting in the original dataset.
    • Adjust ranges: Ensure that columns like AA correlate with your actual data's column endpoints.

    By applying this method, you'll fill out your summary table with the count of matches for each price per store. Make sure the referenced row ($B2:$AA2) corresponds to the correct original row for each store.

  15. User

    i need sum

  16. Assistant

    To calculate the sum of occurrences where each store ID in your summary table has values that match the specified prices in the header (17.3, 15, 20, 30) in the original dataset, you can use Google Sheets formulas to aggregate these values.

    Here's how you can set up your summary table to calculate these sums:

    Step-by-Step Guide

    Original Data Layout

    Assume your original data layout is something like this (starting from A1):

                 |    A    |    B    |    C    | ... |    Y
    ------------------------------------------------------
        1      | Store/Price | 17.3 | 17.3 |    ...    | 30
        2      | 11182         |    0     |    0     |    ...    |
        3      | 11136         |    0     |    0     |    ...    |
        4      | 11190         |    0     |    0     |    ...    |
        ... 
    

    Summary Table Setup

    Assuming your summary starts from A11 like this:

                 |    A    |    B    |    C    |    D    |    E
    ----------------------------------------------------------
        11    | Store/Price | 17.3 |    15   |    20   |    30
        12    | 11182
        13    | 11136
        14    | 11190
        ...
    
    1. Create Formulas for Each Price Category:

      For each store ID, you need to sum the occurrences where the values match each of the four prices (17.3, 15, 20, 30).

    2. Formula for Each Cell:

      • In B12 (for 17.3 with Store ID 11182):

        =SUMIF($B$2:$Z$2, B$11, B12:Z12)
        
      • In C12 (for 15 with Store ID 11182):

        =SUMIF($B$2:$Z$2, C$11, B12:Z12)
        
      • In D12 (for 20 with Store ID 11182):

        =SUMIF($B$2:$Z$2, D$11, B12:Z12)
        
      • In E12 (for 30 with Store ID 11182):

        =SUMIF($B$2:$Z$2, E$11, B12:Z12)
        
    3. Drag the Formulas Down and Across:

      Once you have the formulas set up in the row for 11182, drag them down and across to fill out the table for all store IDs and price categories.

    Explanation:

    • SUMIF function: This sums the values in B12:Z12 that have header matching the one in row 11 (assuming row 2 is your header in actual data). It helps aggregate the store values corresponding to each price.
    • $B$2:$Z$2 should cover all price headers, and B12:Z12 should cover the whole row for the specific store ID in your full dataset.

    Make sure to adjust the cell ranges to match those of the actual spreadsheet you are working with.

  17. User

    ต้องการสูตร ผลรวมของแต่ละราคาในแต่ละ store

  18. Assistant

    หากคุณต้องการคำนวณผลรวมของราคาที่แต่ละ store บันทึกไว้ในข้อมูล Google Sheets ของคุณ คุณสามารถใช้ฟังก์ชัน SUMIF เพื่อหาผลรวมของราคาที่ระบุใน header (เช่น 17.3, 15, 20, 30) ของแต่ละ store ได้

    ข้อมูลเริ่มต้น

    แผ่นข้อมูลของคุณอาจมีลักษณะดังนี้:

                 |    A    |    B    |    C    | ... |    Z
    ------------------------------------------------------
        1      | Store/Price | 17.3 | 17.3 |    ...    | 30
        2      | 11182         |    0     |    0     |    ...    |
        3      | 11136         |    0     |    0     |    ...    |
       ...
    

    ตารางสรุปที่ต้องการสร้าง

    เริ่มจาก A11 เป็นต้นไป:

                 |    A    |    B    |    C    |    D    |    E
    ----------------------------------------------------------
        11    | Store/Price | 17.3 |    15   |    20   |    30
        12    | 11182
        13    | 11136
        14    | 11190
        ...
    

    การตั้งค่าสูตร

    1. สร้างสูตรในเซลล์แต่ละช่องในตารางสรุป:

      • ใน B12 (รวมราคาทั้งหมดที่เป็น 17.3 สำหรับ store 11182):

        =SUMIF($B$1:$Z$1, B$11, B2:Z2)
        
      • ใน C12 (รวมราคาทั้งหมดที่เป็น 15 สำหรับ store 11182):

        =SUMIF($B$1:$Z$1, C$11, B2:Z2)
        
      • ใน D12 (รวมราคาทั้งหมดที่เป็น 20 สำหรับ store 11182):

        =SUMIF($B$1:$Z$1, D$11, B2:Z2)
        
      • ใน E12 (รวมราคาทั้งหมดที่เป็น 30 สำหรับ store 11182):

        =SUMIF($B$1:$Z$1, E$11, B2:Z2)
        
    2. ลากสูตรลงและไปทางขวาเพื่อเติมตาราง:

      • เมื่อสร้างสูตรสำหรับแถวแรกของ 11182 เสร็จแล้ว ให้ลากสูตรลงและไปทางขวาเพื่อใช้กับ store อื่น ๆ ทั้งหมดในตาราง

    คำอธิบายเกี่ยวกับสูตร:

    • ฟังก์ชัน SUMIF จะรวมเฉพาะค่าที่ตรงกับเกณฑ์ที่ระบุ
    • $B$1:$Z$1 คือช่วงของราคาใน header
    • B2:Z2 คือช่วงของข้อมูลของ store นั้น ๆ
    • B$11, C$11, D$11, และ E$11 คือเงื่อนไขราคาที่อยู่ใน header ของตารางสรุป

    ปรับช่วงเซลล์ให้ตรงกับข้อมูลของคุณในแผ่นงานที่กำลังทำงานอยู่จริง ๆ

  19. User

    แสดงผลลัพธ์ของ 11190

  20. Assistant

    ในการหาผลรวมของราคาที่ store ID 11190 บันทึกไว้สำหรับราคาที่ระบุต่างๆ (เช่น 17.3, 15, 20, 30) ให้ทำตามขั้นตอนด้านล่างใน Google Sheets:

    การตั้งค่าข้อมูล

    จากข้อมูลที่คุณได้ให้มา:

                 |    A    |    B    |    C    |    D    |    ...   |     X    |     Y
    ---------------------------------------------------------------------------
        1      | Store/Price | 17.3 | 17.3 | 17.3 |    ...    |   20  |   30
        2      | 11182         |    0     |    0     |    0     |    ...    |    0    |    0
        3      | 11136         |    0     |    0     |    0     |    ...    |    0    |    0
        4      | 11190         |    0     |    0     |    0     |    ...    |   31  |    0
       ...
    

    สูตรในการคำนวณผลรวม

    กำหนดพื้นที่เซลล์ของ 11190 และใช้งาน SUMIF เพื่อคำนวณ:

    1. ผลรวมสำหรับราคาที่ระบุ (e.g., 17.3, 15, 20, 30):

    สูตรตัวอย่าง:

    • สำหรับ 17.3 (สมมติว่าเซลล์ราคาจาก B ถึง H, ยืนยันพื้นที่ราคาในแถวที่ 1):

      =SUMIF($B$1:$Y$1, 17.3, B4:Y4)
      
    • สำหรับ 15 (หากมีแถวที่ราคา 15 อยู่):

      =SUMIF($B$1:$Y$1, 15, B4:Y4)
      
    • สำหรับ 20:

      =SUMIF($B$1:$Y$1, 20, B4:Y4)
      
    • สำหรับ 30:

      =SUMIF($B$1:$Y$1, 30, B4:Y4)
      

    สรุปผลลัพธ์สำหรับ store ID 11190:

    • 17.3: สูตรจะนับจำนวนของค่า 0 ที่อยู่ในคอลัมน์ซึ่งมีราคาเป็น 17.3
    • 15: ทำซ้ำสูตรโดยเปลี่ยนราคาเป็น 15 หากต้องการ
    • 20: ผลรวมในสูตรจะเป็น 31 เนื่องจากมีค่า 31 อยู่ในเซลล์ที่ราคาหัวตารางเท่ากับ 20
    • 30: ค่ารวมจะเป็น 0 หากไม่มีสถานะตรงกัน

    หมายเหตุ:

    • ตรวจสอบให้แน่ใจว่าช่วงเซลล์ในสูตรตรงกับข้อมูลจริง ๆ ในแผ่นงานของคุณ
    • หากราคามีการเปลี่ยนแปลงในตำแหน่งคอลัมน์หรือมีค่าเพิ่มเติมที่ต้องการรวม จงปรับสูตรตามต้องการ