I ran across an interesting problem. I need to count the number of valid
entries and I simply used an IF statement to apply criteria to a column of
cells and copied only those I was interested in counting to the adjacent
column, then used COUNTA( ) to count them.
B12 is equal to =IF(and(A12 <MAX,A12MIN,A12,"")
Didn't work. It counts the "" cells.
If I manually delete one of the "" entries in column B, it doesn't count it.
Is there a simple solution?

Thanks,

Craig

On Aug 11, 7:33 am, "C Brandt" wrote:
I ran across an interesting problem. I need to count the number of valid
entries and I simply used an IF statement to apply criteria to a column of
cells and copied only those I was interested in counting to the adjacent
column, then used COUNTA( ) to count them.
B12 is equal to =IF(and(A12 <MAX,A12MIN,A12,"")
Didn't work. It counts the "" cells.
If I manually delete one of the "" entries in column B, it doesn't count it.
Is there a simple solution?

Thanks,

Craig

Hi Craig,

One way would be to stop using "" and use something like "NO" instead,
then use COUNTIF(B:B,"NO").

Ken Johnson

Or use something else than COUNTA

=SUMPRODUCT(--(B2:B20<""))


--

Regards,

Peo Sjoblom


"Ken Johnson" wrote in message
ups.com...
On Aug 11, 7:33 am, "C Brandt" wrote:
I ran across an interesting problem. I need to count the number of valid
entries and I simply used an IF statement to apply criteria to a column
of
cells and copied only those I was interested in counting to the adjacent
column, then used COUNTA( ) to count them.
B12 is equal to =IF(and(A12 <MAX,A12MIN,A12,"")
Didn't work. It counts the "" cells.
If I manually delete one of the "" entries in column B, it doesn't count
it.
Is there a simple solution?

Thanks,

Craig

Hi Craig,

One way would be to stop using "" and use something like "NO" instead,
then use COUNTIF(B:B,"NO").

Ken Johnson

Every once in a while, I stand back in amazement at solutions that end up
fixing a problem. This is one of those times.

Generally, I research the suggestions, learn something new and apply it to
my problem. Thanks to this group, I have been able to develop some pretty
neat spreadsheets that solve problems faced by the small group of people I
work with.

This solution, while it seems to work beautifully, I cannot, for the life of
me, figure out why or how it works.
Upon review, I viewed SUMPRODUCT as a shortcut to lots of multiplying and
adding, and saw little of value and could not understand how it applied to
my problem. I have learned to never disregard advice given in this forum and
therefore, simply pasted your formula in my spreadsheet with the necessary
address changes to fit, it worked. Why? I have no earthly idea!

Specific questions: What does the -- mean to the formula? What does the
<"" do? and Since I thought that SUMPRODUCT multiplyed "Group A" against
"Group B" then added the answers for a single sum. The Data in the Range
given in the formula is text and I thought that SUMPRODUCT would treat it as
a ZERO.

With all this said: IT WORKED!

Is there a source of wisdom somewhere online that would help me out?

In your debt,
Craig



"Peo Sjoblom" wrote in message
...
Or use something else than COUNTA

=SUMPRODUCT(--(B2:B20<""))


--

Regards,

Peo Sjoblom


"Ken Johnson" wrote in message
ups.com...
On Aug 11, 7:33 am, "C Brandt" wrote:
I ran across an interesting problem. I need to count the number of
valid
entries and I simply used an IF statement to apply criteria to a column
of
cells and copied only those I was interested in counting to the
adjacent
column, then used COUNTA( ) to count them.
B12 is equal to =IF(and(A12 <MAX,A12MIN,A12,"")
Didn't work. It counts the "" cells.
If I manually delete one of the "" entries in column B, it doesn't
count
it.
Is there a simple solution?

Thanks,

Craig

Hi Craig,

One way would be to stop using "" and use something like "NO" instead,
then use COUNTIF(B:B,"NO").

Ken Johnson

Craig,


this part


B2:B20<""


translates to "does not equal blank" (regardless whether the blank is
derived from a totally empty cell or a formula that returns a null string)


and it will return an array of TRUE or FALSE depending on the cell
contents, like this

{FALSE;TRUE;FALSE;FALSE; and so on

where in this case cell number 2 (B3) holds a value that is not blank
returned from this formula

=IF(AND(A3 <MAX,A3MIN,A3,"")

meaning that B3 holds whatever is in A3


so the formula could look like


=SUMPRODUCT(--({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;F ALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FAL SE;FALSE;FALSE}))

by either adding 0, multiplying with 1 or using a unary minus we coerce the
above array of TRUE or FALSE into 1s or 0s


=SUMPRODUCT({0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 })

then SUMPRODUCT will sum those zeros and 1s and in this case return 1




Here's a link


http://www.mcgimpsey.com/excel/formulae/doubleneg.html





--
Regards,

Peo Sjoblom







"C Brandt" wrote in message
...
Every once in a while, I stand back in amazement at solutions that end up
fixing a problem. This is one of those times.

Generally, I research the suggestions, learn something new and apply it to
my problem. Thanks to this group, I have been able to develop some pretty
neat spreadsheets that solve problems faced by the small group of people I
work with.

This solution, while it seems to work beautifully, I cannot, for the life
of
me, figure out why or how it works.
Upon review, I viewed SUMPRODUCT as a shortcut to lots of multiplying and
adding, and saw little of value and could not understand how it applied to
my problem. I have learned to never disregard advice given in this forum
and
therefore, simply pasted your formula in my spreadsheet with the necessary
address changes to fit, it worked. Why? I have no earthly idea!

Specific questions: What does the -- mean to the formula? What does the
<"" do? and Since I thought that SUMPRODUCT multiplyed "Group A" against
"Group B" then added the answers for a single sum. The Data in the Range
given in the formula is text and I thought that SUMPRODUCT would treat it
as
a ZERO.

With all this said: IT WORKED!

Is there a source of wisdom somewhere online that would help me out?

In your debt,
Craig



"Peo Sjoblom" wrote in message
...
Or use something else than COUNTA

=SUMPRODUCT(--(B2:B20<""))


--

Regards,

Peo Sjoblom


"Ken Johnson" wrote in message
ups.com...
On Aug 11, 7:33 am, "C Brandt" wrote:
I ran across an interesting problem. I need to count the number of
valid
entries and I simply used an IF statement to apply criteria to a
column
of
cells and copied only those I was interested in counting to the
adjacent
column, then used COUNTA( ) to count them.
B12 is equal to =IF(and(A12 <MAX,A12MIN,A12,"")
Didn't work. It counts the "" cells.
If I manually delete one of the "" entries in column B, it doesn't
count
it.
Is there a simple solution?

Thanks,

Craig

Hi Craig,

One way would be to stop using "" and use something like "NO" instead,
then use COUNTIF(B:B,"NO").

Ken Johnson

"I see" said the blind man, as he picked up his hammer and saw....

SUMPRODUCT now has value. Significant value!

Thanks for your great explaination and link.

Have a wonderful week,
Craig


"Peo Sjoblom" wrote in message
...
Craig,


this part


B2:B20<""


translates to "does not equal blank" (regardless whether the blank is
derived from a totally empty cell or a formula that returns a null string)


and it will return an array of TRUE or FALSE depending on the cell
contents, like this

{FALSE;TRUE;FALSE;FALSE; and so on

where in this case cell number 2 (B3) holds a value that is not blank
returned from this formula

=IF(AND(A3 <MAX,A3MIN,A3,"")

meaning that B3 holds whatever is in A3


so the formula could look like



=SUMPRODUCT(--({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;F ALSE;FALSE;F
ALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FAL SE}))

by either adding 0, multiplying with 1 or using a unary minus we coerce
the
above array of TRUE or FALSE into 1s or 0s


=SUMPRODUCT({0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 })

then SUMPRODUCT will sum those zeros and 1s and in this case return 1




Here's a link


http://www.mcgimpsey.com/excel/formulae/doubleneg.html





--
Regards,

Peo Sjoblom







"C Brandt" wrote in message
...
Every once in a while, I stand back in amazement at solutions that end
up
fixing a problem. This is one of those times.

Generally, I research the suggestions, learn something new and apply it
to
my problem. Thanks to this group, I have been able to develop some
pretty
neat spreadsheets that solve problems faced by the small group of people
I
work with.

This solution, while it seems to work beautifully, I cannot, for the
life
of
me, figure out why or how it works.
Upon review, I viewed SUMPRODUCT as a shortcut to lots of multiplying
and
adding, and saw little of value and could not understand how it applied
to
my problem. I have learned to never disregard advice given in this forum
and
therefore, simply pasted your formula in my spreadsheet with the
necessary
address changes to fit, it worked. Why? I have no earthly idea!

Specific questions: What does the -- mean to the formula? What does the
<"" do? and Since I thought that SUMPRODUCT multiplyed "Group A"
against
"Group B" then added the answers for a single sum. The Data in the Range
given in the formula is text and I thought that SUMPRODUCT would treat
it
as
a ZERO.

With all this said: IT WORKED!

Is there a source of wisdom somewhere online that would help me out?

In your debt,
Craig



"Peo Sjoblom" wrote in message
...
Or use something else than COUNTA

=SUMPRODUCT(--(B2:B20<""))


--

Regards,

Peo Sjoblom


"Ken Johnson" wrote in message
ups.com...
On Aug 11, 7:33 am, "C Brandt" wrote:
I ran across an interesting problem. I need to count the number of
valid
entries and I simply used an IF statement to apply criteria to a
column
of
cells and copied only those I was interested in counting to the
adjacent
column, then used COUNTA( ) to count them.
B12 is equal to =IF(and(A12 <MAX,A12MIN,A12,"")
Didn't work. It counts the "" cells.
If I manually delete one of the "" entries in column B, it doesn't
count
it.
Is there a simple solution?

Thanks,

Craig

Hi Craig,

One way would be to stop using "" and use something like "NO"
instead,
then use COUNTIF(B:B,"NO").

Ken Johnson