How get the rows indicated by one formula to become the input for.

[Guest]

I have rows of data and I would like to search by one column, if the text
matches, I'd like to then search only those matching rows by the text in a
second column. I only need the count of matches.

 

[Guest]

=COUNTIF(Column,"text")

Regards,

Peo Sjoblom

 

[Guest]

Close

I basically need to nest that. I want to COUNTIF on one column in the row
and then COUNTIF on another column with ONLY the rows that matched the first
COUNTIF. I tried using AND but wasn't able to get it to give me a number
value, I could only get it to output a TRUE value.

Formula that outputs TRUE looks like this:

=AND(COUNTIF('Details - Nov'!J:J,"Fred"),COUNTIF('Details -
Nov'!L:L,"Service"))

Kat

 

[Guest]

Try

=SUMPRODUCT(--('Details - Nov'!J2::J2000="Fred"),--('Details -
Nov'!L2::L2000="Service"))


note that you cannot use the whole column like in countif (J:J) so but it
will work like a COUNTIF with AND, also if you have mutiple criteria replace
the Fred and Service with single cell references like

=SUMPRODUCT(--('Details - Nov'!J2::J2000=A2),--('Details - Nov'!L2::L2000=B2))

where you would type in the criteria, that way you can change criteria
without edititing the formula

Regards,

Peo Sjoblom

 

[Guest]

I pasted in directly from here and it says there is an error in the formula.

 

[Dave Peterson]

For some reason (heavy fingers????), Peo doubled up his colons (and we all know
how painful a doubled up colon can be!):

=SUMPRODUCT(--('Details - Nov'!J2:J2000=A2),--('Details - Nov'!L2:L2000=B2))
or
=SUMPRODUCT(--('Details - Nov'!J2:J2000="Fred"),
--('Details - Nov'!L2:L2000="Service"))
(all one cell)