Share via

countif in filter mod

Anonymous
2023-12-02T08:18:46+00:00

hi

i have a table and want to use count if in filter mod i searched and find this formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A16,ROW(A2:A16)-ROW(A2),,1)), --(A2:A16="To be arranged"))

but this work only in one column what if when using a range

instead of A2:A16 i want use A2:G16 ????

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102K Reputation points Volunteer Moderator
    2023-12-02T23:26:26+00:00

    Hi,

    I am not sure of what you want but try this formula

    =SUM((BYROW(A2:D10,LAMBDA(a,SUBTOTAL(103,a)))>0)*(A2:A10="To be arranged"))

    Hope this helps.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-12-02T14:16:58+00:00

    I tried but didn't work

    Can you please attache a test file ?

    Was this answer helpful?

    0 comments
  3. Nikolino 2,120 Reputation points
    2023-12-02T09:37:13+00:00

    If you are encountering a value error, it's possible that the structure of your data or the specific condition is causing the issue.

    =SUM(IF(SUBTOTAL(3, OFFSET(A2:G16, ROW(A2:A16)-MIN(ROW(A2:A16)),,1)) * (A2:G16="To be arranged"), 1, 0))

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-12-02T09:21:02+00:00

    thanks for your reply

    but your solution doesn't work and gives #value! Error

    Was this answer helpful?

    0 comments
  5. Nikolino 2,120 Reputation points
    2023-12-02T08:38:45+00:00

    If you want to use the COUNTIF function with a filter mode in a table that spans multiple columns (e.g., A2:G16), you can modify the formula accordingly. The SUBTOTAL and OFFSET functions can still be used to achieve this. Here is an example formula:

    =SUMPRODUCT(SUBTOTAL(3, OFFSET(A2:G16, ROW(A2:A16)-MIN(ROW(A2:A16)),,1)), --(A2:G16="To be arranged"))

    Explanation:

    • SUBTOTAL(3, OFFSET(A2:G16, ROW(A2:A16)-MIN(ROW(A2:A16)),,1)): This part of the formula calculates the subtotal for each column in the specified range (A2:G16). The OFFSET function adjusts the reference for each row.
    • --(A2:G16="To be arranged"): This part creates an array of 1s and 0s, where 1 represents rows where the condition is met ("To be arranged").
    • SUMPRODUCT(...): This function multiplies corresponding elements in the two arrays and then sums up the results. It effectively counts the number of rows where both conditions are true.

    This formula considers each column independently when applying the filter condition.

    Remember to adjust the range and condition according to your specific data and requirements. AI was partially deployed to support the text.

    My answers are voluntary and without guarantee!

    Hope this will help you.

    Was this answer helpful?

    0 comments