Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How get the rows indicated by one formula to become the input for.
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. |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Adding Rows to Master Sheet | New Users to Excel | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |