Crazy question, is there a simple macro that will allow me to count how
many times a number occurs on a given day of the week? The current format
of the spread sheet is this:

Column A B C D E F
Day | Date | Series | #1 | #2 | #3
Sun | 1/1/05 | 123 | 1 | 2 | 3

What I want to do is count how many times a number occurs on a given day
of the week without having to manually sort, physically count and rewrite
a plain simple sort routine. Is there any there that can help me with
either a simple macro (or heaven forbid, VBA code)???
Thanks,
Sir Metro

Which number do you want to count? What range do you want to look in?

In your example do you want to count the 2's in col D:F,
and if so do you want to count the 2's only if Col A has , say "Sun"

Will Col D:F have any numbers besides 1,2 or 3?

Do you have a specific cell with the number you are trying to count?

"SirMetro" wrote:

Crazy question, is there a simple macro that will allow me to count how
many times a number occurs on a given day of the week? The current format
of the spread sheet is this:

Column A B C D E F
Day | Date | Series | #1 | #2 | #3
Sun | 1/1/05 | 123 | 1 | 2 | 3

What I want to do is count how many times a number occurs on a given day
of the week without having to manually sort, physically count and rewrite
a plain simple sort routine. Is there any there that can help me with
either a simple macro (or heaven forbid, VBA code)???
Thanks,
Sir Metro

"SirMetro" wrote in message
lkaboutsoftware.com...
Crazy question, is there a simple macro that will allow me to count how
many times a number occurs on a given day of the week? The current format
of the spread sheet is this:

Column A B C D E F
Day | Date | Series | #1 | #2 | #3
Sun | 1/1/05 | 123 | 1 | 2 | 3

What I want to do is count how many times a number occurs on a given day
of the week without having to manually sort, physically count and rewrite
a plain simple sort routine.

=COUNTIF(A2:A100,"Sun")

Is there any there that can help me with
either a simple macro (or heaven forbid, VBA code)???

A macro is VBA, so what is the difference in your mind?

Just to add:
for more than one criteria
=sumproduct(--(A1:A200="Sun"),--(D1:D200=1))

as an example.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
"SirMetro" wrote in message
lkaboutsoftware.com...
Crazy question, is there a simple macro that will allow me to count how
many times a number occurs on a given day of the week? The current
format
of the spread sheet is this:

Column A B C D E F
Day | Date | Series | #1 | #2 | #3
Sun | 1/1/05 | 123 | 1 | 2 | 3

What I want to do is count how many times a number occurs on a given day
of the week without having to manually sort, physically count and
rewrite
a plain simple sort routine.

=COUNTIF(A2:A100,"Sun")

Is there any there that can help me with
either a simple macro (or heaven forbid, VBA code)???

A macro is VBA, so what is the difference in your mind?

1st, I would like to count the quantity a number will occur in D, E & F
independantly of each other based on the day of the week in A.

2nd, Range of numbers in D, E, & F is 0 thru 9.

3rd, in my mind, A "Macro" resides in a tiny little cell, does it's job
with minimal to no further thought on my behalf. "VBA Code" is a bit more
complex and requires a secondary program to write and requires manual
(push a button) effort to execute on a given day.

4th, the Countif doesn't do the job because it only looks at a single
range. The
=SUMPRODUCT(--(A$2:$A$3000="Sun"),--($D$2:$D$3000=1))
doesnt work, I already tried it and it is obviously NOT counting the
correct information. Currently, there is 2859 lines, the net sum of 0 thru
9 is resulting in net totals ranging from 408 to 3080 (net total should be
2859).

=SUMPRODUCT(--(A$2:$A$3000="Sun"),--(($D$2:$D$3000=1)+($E$2:$E3000=1)+($F$2
:$F$3000=1)))

counted all the cells in D, E or F that contained a 1 when column A
contained SUN.

If that doesn't work for you, then there is a misunderstanding on the
requirement.

--
Regards,
Tom Ogilvy

"SirMetro" wrote in message
lkaboutsoftware.com...
1st, I would like to count the quantity a number will occur in D, E & F
independantly of each other based on the day of the week in A.

2nd, Range of numbers in D, E, & F is 0 thru 9.

3rd, in my mind, A "Macro" resides in a tiny little cell, does it's job
with minimal to no further thought on my behalf. "VBA Code" is a bit more
complex and requires a secondary program to write and requires manual
(push a button) effort to execute on a given day.

4th, the Countif doesn't do the job because it only looks at a single
range. The
=SUMPRODUCT(--(A$2:$A$3000="Sun"),--($D$2:$D$3000=1))
doesnt work, I already tried it and it is obviously NOT counting the
correct information. Currently, there is 2859 lines, the net sum of 0 thru
9 is resulting in net totals ranging from 408 to 3080 (net total should be
2859).

My intent is to count each column (D, E & F) independantly of each other,
not combined like your formula.
Simply speaking, I am running a spreadsheet to track Cash 3 numbers and I
want to be able to have a simple macro that will fill in a preset table
that shows me how many times 1 fell on Sun, Mon, etc..., without me
haveing to literally write and rewrite after I did a sort to break about
the days from each other each and every time I update my spreadsheet.
Sir Metro

Then do them independently

=SUMPRODUCT(--(A$2:$A$3000="Sun"),--($D$2:$D$3000=1))
=SUMPRODUCT(--(A$2:$A$3000="Sun"),--($E$2:$E$3000=1))
=SUMPRODUCT(--(A$2:$A$3000="Sun"),--($F$2:$F$3000=1))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SirMetro" wrote in message
lkaboutsoftware.com...
My intent is to count each column (D, E & F) independantly of each other,
not combined like your formula.
Simply speaking, I am running a spreadsheet to track Cash 3 numbers and I
want to be able to have a simple macro that will fill in a preset table
that shows me how many times 1 fell on Sun, Mon, etc..., without me
haveing to literally write and rewrite after I did a sort to break about
the days from each other each and every time I update my spreadsheet.
Sir Metro

3rd, in my mind, A "Macro" resides in a tiny little cell, does it's job
with minimal to no further thought on my behalf. "VBA Code" is a bit more
complex and requires a secondary program to write and requires manual
(push a button) effort to execute on a given day.

That is what is normally called a FORMULA. It isn't a macro.