-1

I need help with COUNTIFS function.

There are values in column B: a,b,c,d,e,f,.... I want to use COUNTIFS function to count items in column A, if the values in column B is included in range {a,c,f}

1
  • You say you want to count items in A. Do you mean count qualifying rows or count items contained in cells in column A? What do those cells contain? Does column B contain one item per cell or a list per cell? Is the criterion that a cell in B contain any one or more items in the list, it doesn't matter how many as long as it is at least one? If you're qualifying rows based on B, what is going on in A that would affect anything (why isn't it just the count of qualifying cells in B? You call {a,c,f} a range, which implies intermediate values. Do you mean the list of those three items?
    – fixer1234
    Feb 1, 2019 at 8:05

1 Answer 1

1

To count how many times a,c or f appear in Column B use:

=SUM(COUNTIFS(B:B,{"a","c","f"}))

To sum the values of Column A where a,c or f appears in Column B use:

=SUM(SUMIFS(A:A,B:B,{"a","c","f"}))
2
  • I need to count items in column A, if corresponding row in column B contain items in range {"a","c","f"}. Why do you need to use SUM when counting items in column A?
    – alwbtc
    Feb 13, 2019 at 21:35
  • Where I used { } it creates an array, it will count instances of "a", then "c", and it will SUM the counts. it would be the same as doing 3 x OUNTIFS and adding them together
    – PeterH
    Feb 14, 2019 at 7:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .