countif using two variable
 

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

If you are using Excel 2007 then you can use COUNTIFS

Following will also give you the required count
=SUMPRODUCT(--(A1:A1000="your constant text here"),--(B1:B1000=Sheet2!A1))

Assuming variable text is in Cell A1 of Sheet2

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

=SUMPRODUCT(--(A1:A1000="x")*(B1:B1000=Sheet2!A1))

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

using 2003

"Sheeloo" wrote:

If you are using Excel 2007 then you can use COUNTIFS

Following will also give you the required count
=SUMPRODUCT(--(A1:A1000="your constant text here"),--(B1:B1000=Sheet2!A1))

Assuming variable text is in Cell A1 of Sheet2

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

i am getting #N/A

here is what I have:

=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$6:$B$ 609='Info'!$C6))


"Mike" wrote:

=SUMPRODUCT(--(A1:A1000="x")*(B1:B1000=Sheet2!A1))

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

Sumproduct formula will work in 2003. Did you try it?

"Memphus01" wrote:

using 2003

"Sheeloo" wrote:

If you are using Excel 2007 then you can use COUNTIFS

Following will also give you the required count
=SUMPRODUCT(--(A1:A1000="your constant text here"),--(B1:B1000=Sheet2!A1))

Assuming variable text is in Cell A1 of Sheet2

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

sorry- yes, see below:

"Sheeloo" wrote:

Sumproduct formula will work in 2003. Did you try it?

"Memphus01" wrote:

using 2003

"Sheeloo" wrote:

If you are using Excel 2007 then you can use COUNTIFS

Following will also give you the required count
=SUMPRODUCT(--(A1:A1000="your constant text here"),--(B1:B1000=Sheet2!A1))

Assuming variable text is in Cell A1 of Sheet2

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

Try this
=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$5:$B$ 609='Info'!$C6))


"Memphus01" wrote:

i am getting #N/A

here is what I have:

=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$6:$B$ 609='Info'!$C6))


"Mike" wrote:

=SUMPRODUCT(--(A1:A1000="x")*(B1:B1000=Sheet2!A1))

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?

countif using two variable
 

Great- thanks- works perfectly-

ok, now I have an even more complex question that is somewhat related...

in the formula below I get a count...

now I would like to get an average of column S on "Ratings" if the above
criteria are met...

in the past I would do a sumif/countif... but I have not done it this way
before.

"Mike" wrote:

Try this
=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$5:$B$ 609='Info'!$C6))


"Memphus01" wrote:

i am getting #N/A

here is what I have:

=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$6:$B$ 609='Info'!$C6))


"Mike" wrote:

=SUMPRODUCT(--(A1:A1000="x")*(B1:B1000=Sheet2!A1))

"Memphus01" wrote:

i am trying to create a countif statement that allows me to use two variable-

I have data in rows 1-1000

I would like to countif Column A=x and Column B=a variable from a cell on
another sheet -

The first variable is a text value, it is constant, so I have no issue
there, when I try and use the variable text from the cell on the other sheet
is where I run into trouble...

is this possible?