Ask Your Question
0

Counting number of times two adjacent cells match a certain criteria [closed]

asked 2014-12-12 21:55:46 +0100

MrMusAddict gravatar image

Hello, I'm looking for a way to count the number of times two adjacent cells in two separate columns match. In this example, column A contains vehicle types ("SUV", "Sedan", "Truck"), and in column B contains vehicle color ("Red", "Blue", "Black").

I want to count the number of black SUVs:

       A        B
1    SUV      Red
2    Truck    Blue
3    Sedan    Red
4    SUV      Black
5    Sedan    Blue
6    Truck    Blue
7    SUV      Black

In this case, the function should print 2.

Is there a way to do this?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-07 01:56:57.666166

1 Answer

Sort by » oldest newest most voted
1

answered 2014-12-12 22:55:59 +0100

Lupp gravatar image
      A                B                     C
 1   KIND_ON        COLOUR_ON      2
 2   suv            black          =SUMPRODUCT(UPPER(A2)=UPPER(A7:A13);
                                          UPPER(B2)=UPPER(B7:B13))
 ...
 6   KIND           COLOUR  
 7   SUV            Red 
 8   Truck          Blue    
 9   Sedan          Red 
 10  SUV            Black   
 11  Sedan          Blue
 12  Truck          Blue    
 13  SUV            Black
edit flag offensive delete link more

Comments

Thank you, this worked! I would upvote you, but I don't have enough points :/

MrMusAddict gravatar imageMrMusAddict ( 2014-12-12 23:26:17 +0100 )edit

Hi @MrMusAddict, if answer is correct, instead try to vote mark the answer as correct 

m.a.riosv gravatar imagem.a.riosv ( 2014-12-13 00:27:56 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2014-12-12 21:55:46 +0100

Seen: 240 times

Last updated: Dec 12 '14