| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with tweaking formula
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro tweaking | Excel Discussion (Misc queries) | |||
| Tweaking formula? | Excel Discussion (Misc queries) | |||
| Golf Score Formula needs tweaking | Excel Discussion (Misc queries) | |||
| "AutoPrint" type macro requires tweaking | Excel Discussion (Misc queries) | |||
Linear Mode
