# COUNTIF's, IF, & AND functions

#### NABAS

##### New Member
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?

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)))

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

NABAS said:
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))

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.

Then use Aladin's formula.

Thanks Guys,

Aladdin's formula is working wonderfully for me

Replies
1
Views
111
Replies
5
Views
331
Replies
1
Views
478
Replies
5
Views
146
Replies
5
Views
161

1,203,541
Messages
6,056,006
Members
444,840
Latest member
RazzelDazel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back