countif using two variable

[Memphus01]

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?

 

[Sheeloo]

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

 

[Mike]

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

 

[Memphus01]

using 2003

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]

i am getting #N/A

here is what I have:

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

 

[Sheeloo]

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

 

[Memphus01]

sorry- yes, see below:

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

 

[Mike]

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

 

[Memphus01]

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.