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)
katgolightly wrote:
I pasted in directly from here and it says there is an error in the formula.
"Peo Sjoblom" wrote:
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
"katgolightly" wrote:
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
"Peo Sjoblom" wrote:
=COUNTIF(Column,"text")
Regards,
Peo Sjoblom
"katgolightly" wrote:
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.
--
Dave Peterson
|