Macro to Count how many times a number occurs on a day

[SirMetro]

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

 

[Guest]

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?

 

[Bob Phillips]

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?

 

[Tom Ogilvy]

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

as an example.

 

[SirMetro]

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).

 

[Tom Ogilvy]

=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.

 

[SirMetro]

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

 

[Bob Phillips]

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)

 

[Myrna Larson]

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.