Re Excel Array Formula
 

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

COUNTIF

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column B
is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

I have tried transposing countif for sumif but it doesn't work. I have also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

I have a table that lists when clients are contacted and there response.
I need to collate the information according to the date and the response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect however the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

Further to what I have written I have been trying out different things and
came up with the following:
Private Sub Worksheet()
ActiveCell.FormulaR1C1 = _
"=IF(Range(D12:D2000)=""Y"",Range(T12:T2000)=1 "
End Sub
The idea is if I can't get a formula to collate the information the way I
need it to happen then maybe I can try to convert the "Y" into a numeral in a
separate column and then use the sumif formula. I know I can do this
conversion on individual cells but I would rather do it on a field basis as
it occupies less space.

"John D" wrote:

I have a table that lists when clients are contacted and there response.
I need to collate the information according to the date and the response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect however the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

PS. The formula I mentioned doesn't work but something along this line would
do.

"John D" wrote:

Further to what I have written I have been trying out different things and
came up with the following:
Private Sub Worksheet()
ActiveCell.FormulaR1C1 = _
"=IF(Range(D12:D2000)=""Y"",Range(T12:T2000)=1 "
End Sub
The idea is if I can't get a formula to collate the information the way I
need it to happen then maybe I can try to convert the "Y" into a numeral in a
separate column and then use the sumif formula. I know I can do this
conversion on individual cells but I would rather do it on a field basis as
it occupies less space.

"John D" wrote:

I have a table that lists when clients are contacted and there response.
I need to collate the information according to the date and the response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect however the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question and I
believe that it might benefit what I am trying to do however my column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

You never did give a sample of your layout. IF?? you have dates in col B and
Y/N is col C and you want to COUNT them, then try this keeping in mind that
you cannot use entire columns in the formula. Assumes your date in B1.
=sumproduct((b2:b22=b1)*(c2:c22="Y"))

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have a table that lists when clients are contacted and there response.
I need to collate the information according to the date and the response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect however
the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have
also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question
and I
believe that it might benefit what I am trying to do however my
column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

Sorry its taken me a while to get back however here's the layout:
Worksheet
Col"B" Col"C" Col"D" Col"E" Col"F" Col"G" Col"H" Col"I" Col"J"
1st Vst Photo Demo 2nd Vst Result Com- Signup Price
Name Date Y/N Y?N Date FU/SU ment Date $am
Report
Col"A" Col"B" Col"C" Col"D" Col"E" Col"F"
Wk Date Photo Demo Sign up Fol Up Tot $
The report is a summation of daily activities with number of photos taken,
demo's done, sign ups, follow ups, and total $ in sales.

Although the date in the report is static and a separate formula can be used
for each day of the week, the worksheet is liable to have any date on any row.
I shall try your formula suggestion as it looks as though it may work.
Thank you very much Don in advance. I shall write again to let you know how
I go.

"Don Guillett" wrote:

You never did give a sample of your layout. IF?? you have dates in col B and
Y/N is col C and you want to COUNT them, then try this keeping in mind that
you cannot use entire columns in the formula. Assumes your date in B1.
=sumproduct((b2:b22=b1)*(c2:c22="Y"))

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have a table that lists when clients are contacted and there response.
I need to collate the information according to the date and the response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect however
the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have
also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question
and I
believe that it might benefit what I am trying to do however my
column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

Just tried out your formula suggestion Don.
Bloody Bewdy it works! Perfect. Can't thank you enough.

"John D" wrote:

Sorry its taken me a while to get back however here's the layout:
Worksheet
Col"B" Col"C" Col"D" Col"E" Col"F" Col"G" Col"H" Col"I" Col"J"
1st Vst Photo Demo 2nd Vst Result Com- Signup Price
Name Date Y/N Y?N Date FU/SU ment Date $am
Report
Col"A" Col"B" Col"C" Col"D" Col"E" Col"F"
Wk Date Photo Demo Sign up Fol Up Tot $
The report is a summation of daily activities with number of photos taken,
demo's done, sign ups, follow ups, and total $ in sales.

Although the date in the report is static and a separate formula can be used
for each day of the week, the worksheet is liable to have any date on any row.
I shall try your formula suggestion as it looks as though it may work.
Thank you very much Don in advance. I shall write again to let you know how
I go.

"Don Guillett" wrote:

You never did give a sample of your layout. IF?? you have dates in col B and
Y/N is col C and you want to COUNT them, then try this keeping in mind that
you cannot use entire columns in the formula. Assumes your date in B1.
=sumproduct((b2:b22=b1)*(c2:c22="Y"))

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have a table that lists when clients are contacted and there response.
I need to collate the information according to the date and the response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect however
the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I have
also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous question
and I
believe that it might benefit what I am trying to do however my
column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?

Re Excel Array Formula
 

"Bloody Bewdy" is a new one for a Texan. Is that good?
Glad to help.

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
Just tried out your formula suggestion Don.
Bloody Bewdy it works! Perfect. Can't thank you enough.

"John D" wrote:

Sorry its taken me a while to get back however here's the layout:
Worksheet
Col"B" Col"C" Col"D" Col"E" Col"F" Col"G" Col"H" Col"I"
Col"J"
1st Vst Photo Demo 2nd Vst Result Com- Signup
Price
Name Date Y/N Y?N Date FU/SU ment Date
$am
Report
Col"A" Col"B" Col"C" Col"D" Col"E" Col"F"
Wk Date Photo Demo Sign up Fol Up Tot $
The report is a summation of daily activities with number of photos
taken,
demo's done, sign ups, follow ups, and total $ in sales.

Although the date in the report is static and a separate formula can be
used
for each day of the week, the worksheet is liable to have any date on any
row.
I shall try your formula suggestion as it looks as though it may work.
Thank you very much Don in advance. I shall write again to let you know
how
I go.

"Don Guillett" wrote:

You never did give a sample of your layout. IF?? you have dates in col
B and
Y/N is col C and you want to COUNT them, then try this keeping in mind
that
you cannot use entire columns in the formula. Assumes your date in B1.
=sumproduct((b2:b22=b1)*(c2:c22="Y"))

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have a table that lists when clients are contacted and there
response.
I need to collate the information according to the date and the
response,
this is why the formula "=SumIf(B:B,23/5/07,C:C) sounded perfect
however
the
response I am trying to collate is either "Y" or "N"

"Don Guillett" wrote:

=COUNTIF(B:B,"I") counts the number of Y's in col B.
What are you trying to do?

--
Don Guillett
SalesAid Software

"John D" wrote in message
...
I have tried transposing countif for sumif but it doesn't work. I
have
also
tried
=sumif(A:A,51008,countif(B:B,"Y")) and this causes an error.

"Ben McBen" wrote:

CountIf!

"John D" wrote:

=SumIF(A:A,51008,B:B)
I have just read this formula in an answer to a previous
question
and I
believe that it might benefit what I am trying to do however my
column
B is
the letter Y and not a number so I need to count them.
Is there a count alternative to this formula?