countif using two variable

  • Thread starter Thread starter Memphus01
  • Start date Start date
M

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?
 
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
 
using 2003

Sheeloo said:
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
Click to expand...
 
i am getting #N/A

here is what I have:

=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$6:$B$609='Info'!$C6))
 
Try this
=SUMPRODUCT(--('Ratings'!$D$5:$D$609="Sr.*")*('Ratings'!$B$5:$B$609='Info'!$C6))
 
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.
 
Back
Top