Forum Discussion

lp_rekor's avatar
lp_rekor
Copper Contributor
Mar 22, 2022

Formula to count cross-referenced values

I'm working through a situation similar to the following.  A table with two data points:

 

 

CAR BODY STYLECAR COLOR
SedanBlue
CoupeBlue
SedanRed
CoupeRed
SedanBlue

 

I want to count the number of blue sedans.  I'm trying to create an array of column A values where column B is Blue, then count the number of values in that array that are Sedans.  The formula I have is:

 

 

=COUNTIF((FILTER(A2:A6,B2:B6="Blue")),"Sedan")

 

 

I'd expect a count of 2 from the above scenario.  Instead, Excel pops the generic "There's a problem with this formula" error when I try to apply it.  I can't figure out what the problem is.  Any ideas, or other ways I could do this without tripping over weird syntax issues?

excel
Formulas and Functions
office 365

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 22, 2022

    lp_rekor 

    =COUNTA(FILTER(A2:A6,(B2:B6="Blue")*(A2:A6="Sedan")))

     You can try this formula which seems to work in my sheet.

Resources