COUNTIF's, IF, & AND functions

[NABAS]

I am trying to figure out a code I can use in excel that will allow me to compare a sell in one row with one figure and a sell in another row with another figure. Once I have done that I want to be able to count all the rows that were true for both of criteria.

as an example I have data in columns B and C

I want excel to look in colum B row 3 and see if their is a value of lets say 4
then look in column C row 3 and see if their is a value of say 2
I then want excel to do the same for rows 3-23 and count all the instances that both values appear together. how can I do this without creating a macro, or do I necesarrily need to use a macro?

 

[Zack Barresse]

Hello, welcome to the board!

You don't need a macro, not if you don't want. You can use native functions for this. Try this ...

=SUMPRODUCT(--(B3:B23=C3:C23),--NOT(ISBLANK(B3:B23)))

 

[BrianM]

You could also add a column...Concatenate B3&C3 in this column, copy down... and they use the following formula...=COUNTIF(D3:D23,42)

Brian

 

[Aladin Akyurek]

I am trying to figure out a code I can use in excel that will allow me to compare a sell in one row with one figure and a sell in another row with another figure. Once I have done that I want to be able to count all the rows that were true for both of criteria.

as an example I have data in columns B and C

I want excel to look in colum B row 3 and see if their is a value of lets say 4
then look in column C row 3 and see if their is a value of say 2
I then want excel to do the same for rows 3-23 and count all the instances that both values appear together. how can I do this without creating a macro, or do I necesarrily need to use a macro?

Reading your description differently than Zack, but the point is that you can do a multi-conditional count with a formula like:

=SUMPRODUCT(--($A$3:$A$23=4),--($B$3:$B$23=2))

 

[NABAS]

OK if i'm reading that right then the code would look in B3 and if B3 equals C3 then it would add 1 to the counter and so on down till row 23.

What if I want to know if B3=4 and C3=2, then to add one to the counter and not to add anything to the counter when lets say B4=3 and C3=2. How can I do that?

Lets say this is my sheet

A B C D E F
1 3 4 2 4 3 4
2 4 2 2 5 3 2
3 4 2 4 3 5 1
4 1 4 2 6 2 1
5 3 4 2 6 1 4

I want the spreadsheet to count every time a row has the value of 4 in the B colum and 2 in the C Column, so for this example the final value would 3 because it occurs in rows 1, 4, 5.

 

[Zack Barresse]

Then use Aladin's formula.

 

[NABAS]

Thanks Guys,

Aladdin's formula is working wonderfully for me