CountIF with two conditions?! 4

[Paco75]

Hi,

I need to count rows where the column A equals a value and C equals another! For now I got this function :

=COUNTIF(Field1!A:A;"Value1&quot

Is it possible to count rows by looking for two values?

thanks a lot!

 

[meintsi]

check out the AND function...... *Remember.......
If you don't use your head,
your going to have to use your feet.

 

[nfpk]

I think ther easiest way should be to fisrt use an advanced filter and then count the values...

The Database
Value 1 Value 2
1 2
2 3
1 2
4 1

Your criteria
Value 1 Value 2
1 2


Advanced filter
Value 1 Value 2
1 2
1 2

And then use the count function.....

nfpk

 

[KenWright]

=SUMPRODUCT((A1:A100="xyz&quot*(C1:C100="abc&quot)

or with criteria in cells D1 & D2

=SUMPRODUCT((A1:A100=D1)*(C1:C100=D2))

Regards
Ken............

 

[Paco75]

What I need is not Adding the values together... I look to count the number of lines entered witch meet two conditions.

It should be something like :
=COUNTIF(WorkSheet!A:A;"Criteria1",C:C;"Criteria2&quot

but I tried many syntax and no one works. Im using Excel v10.3506.3501 SP-1 (Excel XP I think)

 

[DeLaMartre]

If you use the array formula (remember to enter it by pressing Control/Shift/Enter), it will count only the rows where both criteria are true:

=SUM((C5:C16="abc&quot*(D516="xyz&quot)

Please let me know if that helps.

Best of Luck! -Bob in California

 

[KenWright]

Paco, did you even try the formula????

The answers exploit the fact that Excel interprets TRUE as 1 and FALSE as 0. Take the formula below:-

=SUMPRODUCT((A9:A20="xyz&quot*(B9:B20="abc&quot)

This sets up an array that gives you something that looks like this (depending on the variables of course):-

A B
9 TRUE * FALSE
10 FALSE * FALSE
11 TRUE * TRUE
12 TRUE * TRUE
13 TRUE * FALSE
14 TRUE * TRUE
15 TRUE * TRUE
16 FALSE * TRUE
17 TRUE * TRUE
18 TRUE * TRUE
19 TRUE * TRUE
20 TRUE * TRUE

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
-------------
8

and the SUM bit just adds up all the end values of the products, giving you 8 rows where it meets both criteria.

Regards
Ken.........

 

[DeLaMartre]

Ken,

You deserve a star for all of the time (and expertise) you have devoted to the explanation of this solution. -Bob in California

 

[KenWright]

You are too kind. Just as an addendum, you can also expand the formula to add up values in a 3rd/4th/5th column etc by simply adding that Column to the formula such as this:-

=SUMPRODUCT((A9:A20="xyz&quot*(B9:B20="abc&quot*(C9:C20))

Last set of Brackets around the C9:C20 are unnecessary really, but I think it helps to make it more readable.

Can have numerous columns in this - Criteria to your heart's content.

Regards
Ken..............

 

[Paco75]

Thanks Ken,

I tried it and It works well. At first I was thinking it was a simple product of the values...

thanks a lot!

 

[KenWright]

My pleasure - It only adds up values if you don't include the Boolean logic on one of the ranges such as ="xyz" etc

Regards
Ken..............