Counting with 2 conditions

[Outline]

Hello,

I need to count the word "upper" in two ranges, whether "upper" appears in column L or in column n, or both. I need to know the unique number of "uppers" for 2300+ records.

L2:l2335 and n2:n2335


EX:
L column N column COMMENTARY
record 1 upper upper This would count as 1
record 2 upper 800 This would be 1
record 3 798 "under" This would be 0

therefore, my total count would be 2


Can you help with the correct formula, please

 

[edvwvw]

Try this

=OR(L2="upper",n2="upper")*1

drag down

edvwvw

 

[Outline]

Thanks, worked


However, can I use one formula (such as countif) without adding a separate column to sum?

 

[Aladin Akyurek]

Hello,

I need to count the word "upper" in two ranges, whether "upper" appears in column L or in column n, or both. I need to know the unique number of "uppers" for 2300+ records.

L2:l2335 and n2:n2335


EX:
L column N column COMMENTARY
record 1 upper upper This would count as 1
record 2 upper 800 This would be 1
record 3 798 "under" This would be 0

therefore, my total count would be 2


Can you help with the correct formula, please

Try...

=SUMPRODUCT((ISNUMBER(SEARCH("upper",L2:L18))+ISNUMBER(SEARCH("upper",N2:N18))>0)+0)

Adjust the ranges to suit...

 

[RSXchin]

nevermind, read it wrong

 

[edvwvw]

=(SUMPRODUCT(--(L2:L2335="upper"))+SUMPRODUCT(--(N2:N2335="upper")))-(SUMPRODUCT(--(L2:L2335="upper")*(N2:N2335="upper")))

edvwvw

 

[T. Valko]

Hello,

I need to count the word "upper" in two ranges, whether "upper" appears in column L or in column n, or both. I need to know the unique number of "uppers" for 2300+ records.

L2:l2335 and n2:n2335


EX:
L column N column COMMENTARY
record 1 upper upper This would count as 1
record 2 upper 800 This would be 1
record 3 798 "under" This would be 0

therefore, my total count would be 2


Can you help with the correct formula, please

Try this...

P2 = upper

=SUMPRODUCT(SIGN(ISNUMBER(SEARCH(P2,L2:L10))+ISNUMBER(SEARCH(P2,N2:N10))))