Use countif with multiple criteria

[behedwin]

Hey

Id like to know how to use the countif formula with multiple criteria and count just the first letter/number of a cell.

This is an example of what i have

color | number

In the color column i have a mix of colors, there can be dubplicates....
blue
red
blue
red
yellow
blue
yellow
yellow

in the number column i have a mix of differnt type of numbers....
1
1
2a
1a
2b
3
3
3a

so now my question is

I want to present in a cell how many cells in the column number starts with "1" and also have blue color column?

My solution is to use countif with multiple criteria

=COUNTIF(A:A,"Blue","B:B,"1*")

i am using the * symbol to indicate that i want to include rows that just have 1 and also rows 1a or 1bcd or whatever, it starts with 1.

But this does not seem to work very well...
It works in a way, but it skips, if there are values no values with a second letter or number... like it skips cells with just the number 1
And if i remove the * symbol it skips cells that does have a second letter/number.

 

[steve the fish]

For the countifs to work your numbers will need to be text. Or you could use this:

=SUMPRODUCT(--($A$1:$A$10="Blue"),--(LEFT($B$1:$B$10)="1"))

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
2	blue	1	red	2
3	red	1	1
4	blue	2a
5	red	1a
6	yellow	2b
7	blue	3
8	yellow	3
9	yellow	3a
Sheet3

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(($A$2:$A$9=C2)*(LEFT($B$2:$B$9)*1=C3))

 

[behedwin]

For the countifs to work your numbers will need to be text. Or you could use this:

=SUMPRODUCT(--($A$1:$A$10="Blue"),--(LEFT($B$1:$B$10)="1"))

Great stuff
this worked very well.