Forum Discussion

pycharge's avatar
pycharge
Copper Contributor
Apr 29, 2024
Solved

Excel: Lookup '1' and return multiple values

This seems rather simple be cannot currently find a solve for this. I am currently in a spreadsheet, which has a column that is returning a binary value on the basis of random sampling. I need to l...
BI & Data Analysis
excel
Formulas and Functions
  • MAngosto's avatar
    MAngosto
    Apr 29, 2024

    Hello pycharge ,

     

    You can simply use the FILTER() function!

    Use FILTER(A1:A50,B1:B50=1)

    Where A1:A50 is the range of item numbers and B1:B50 is the range of the binary values.

bb5931's avatar
bb5931
Copper Contributor
Feb 19, 2025

Hi MAngosto ,

I have a similar question to the OP about return multiple matching values but I'm trying to find a function that counts how many cells contain a matching value (rather than return each of their individual values). Specifically I would like a function to count how many cells in B1:B20 contain "James" where corresponding value in A1:A20 equals "Yes".

Please see attached screenshot to illustrate what I would like.

Thanks for your help!

bb5931's avatar
bb5931
Copper Contributor
Feb 20, 2025

Found out! Just a simple COUNTIFS(A:A,"Yes",B:B,"James") !!

Resources