Help with tweaking formula
 

Community,

Using the base of (which works):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")


How would I write:

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3="N" OR "L"
with no reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3="N" or "L"))


=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3G3 with no
reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3G3))


Thanks in advance,

John

Help with tweaking formula
 

Hello, please see comments within:

Johndb wrote:
Community,

Using the base of (which works):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")


How would I write:

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3="N" OR "L"
with no reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3="N" or "L"))

This logic does not make sense. If H3:N3 are numbers, they cannot be
text ("N", "L"). These mutually exclusive conditions will always return
zero. What are you trying to achieve here?

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3G3 with no
reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3G3))

Should work exactly as you described, to wit: count how many cells in
H3:N3 are numbers and are greater than G3.

Help with tweaking formula
 

SMartin,

H3:N3 can be Numbers, Text, or Blanks. These formula variations are being
used for conditional formatting.

Hope that helps.

Thanks,

John

"smartin" wrote:

Hello, please see comments within:

Johndb wrote:
Community,

Using the base of (which works):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")


How would I write:

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3="N" OR "L"
with no reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3="N" or "L"))

This logic does not make sense. If H3:N3 are numbers, they cannot be
text ("N", "L"). These mutually exclusive conditions will always return
zero. What are you trying to achieve here?

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3G3 with no
reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3G3))

Should work exactly as you described, to wit: count how many cells in
H3:N3 are numbers and are greater than G3.

Help with tweaking formula
 

Hi John,

It will be helpful if you restate your problem in terms of the
logical/business requirements instead of suggesting a formula as an answer.

To that end, is this an accurate interpretation?:

'Count cells H3:N3 where H3:N3 is a number OR H3:N3 equals "N" or "L"'?

Best Regards

Johndb wrote:
SMartin,

H3:N3 can be Numbers, Text, or Blanks. These formula variations are being
used for conditional formatting.

Hope that helps.

Thanks,

John

"smartin" wrote:

Hello, please see comments within:

Johndb wrote:
Community,

Using the base of (which works):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")


How would I write:

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3="N" OR "L"
with no reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3="N" or "L"))
This logic does not make sense. If H3:N3 are numbers, they cannot be
text ("N", "L"). These mutually exclusive conditions will always return
zero. What are you trying to achieve here?

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3G3 with no
reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3G3))
Should work exactly as you described, to wit: count how many cells in
H3:N3 are numbers and are greater than G3.

Help with tweaking formula
 

http://groups.google.com/group/micro...f6069f8?hl=en#

--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Hi John,

It will be helpful if you restate your problem in terms of the
logical/business requirements instead of suggesting a formula as an
answer.

To that end, is this an accurate interpretation?:

'Count cells H3:N3 where H3:N3 is a number OR H3:N3 equals "N" or "L"'?

Best Regards

Johndb wrote:
SMartin,

H3:N3 can be Numbers, Text, or Blanks. These formula variations are
being used for conditional formatting.

Hope that helps.

Thanks,

John

"smartin" wrote:

Hello, please see comments within:

Johndb wrote:
Community,

Using the base of (which works):

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")


How would I write:

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3="N" OR
"L" with no reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3="N" or "L"))
This logic does not make sense. If H3:N3 are numbers, they cannot be
text ("N", "L"). These mutually exclusive conditions will always return
zero. What are you trying to achieve here?

=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S")
H3:N3G3
with no reference to B3 at all, such as:
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3G3))
Should work exactly as you described, to wit: count how many cells in
H3:N3 are numbers and are greater than G3.

Help with tweaking formula
 

T. Valko wrote:
http://groups.google.com/group/micro...f6069f8?hl=en#


I see. Thanks for that!