Count items based on two criteria

[Harry Flashman]

Hi, can anyone help with this please
I have a table that lists articles published over a month.
I have two (relevant) columns:

Column A (Pulications)
The Age
The Age
Sydney Morning Herald
The Age
The Bulletin
The Examiner
(etc - the list runs for hundreds - each represents an article and a
publication may appear more than once)


Column B (Day of the week) [Btw originally the data for this was in
date format, but I am just using the days of the week as text -
because I want to count the days of the week]
Saturday
Saturday
Wednesday
Saturday
Friday
(etc - each represents the day of the week and article appeared, and a
day of the week may appear more than once)

I know how to produce a little table that will tell me how many
Saturdays, Sundays etc.
Column F Column G
Saturday =countif($B:$B,F2)
Sunday =countif($B:$B,F3)
Monday = countif($B:$B,F4)

Now I want to produce a table that lists for each publication how many
articles appeared for each day of the week.
It will have the publications in the first column and the days of the
week in the next 7 columns

Saturday | Sunday | Monday | etc
The Age 23 24 17 etc
The Courier Mail

I hope this is clear.
Is there some simple formula that I can use?

Regards,
Harry

 

[papou]

Hello Harry
With defined names "Publication" to refer to your data range in column A**,
"DayOfWeek" to refer to your data range in column B**, C2 containing
Publication criteria, D containing Day of week criteria, formula:

=SUMPRODUCT((Publication=C2)*(DayOfWeek=D1))

**Nb: Defined names must refer to same dimension ranges (eg Publication
=A1:A500, DayOfWeek=B1:B500) and NOT entire columns range(eg A:A, B:B),
otherwise SUMPRODUCT function will not work.

You may also consider using pivot tables.

HTH
Cordially
Pascal

 

[Wingco]

Hello Harry
With defined names "Publication" to refer to your data range in column A**,
"DayOfWeek" to refer to your data range in column B**, C2 containing
Publication criteria, D containing Day of week criteria, formula:

=SUMPRODUCT((Publication=C2)*(DayOfWeek=D1))

**Nb: Defined names must refer to same dimension ranges (eg Publication
=A1:A500, DayOfWeek=B1:B500) and NOT entire columns range(eg A:A, B:B),
otherwise SUMPRODUCT function will not work.

You may also consider using pivot tables.

HTH
Cordially
Pascal

"Harry Flashman" <[email protected]> a écrit dans le message de (e-mail address removed)...

Hi, can anyone help with this please
I have a table that lists articles published over a month.
I have two (relevant) columns:

Column A (Pulications)
The Age
The Age
Sydney Morning Herald
The Age
The Bulletin
The Examiner
(etc - the list runs for hundreds - each represents an article and a
publication may appear more than once)

Column B (Day of the week) [Btw originally the data for this was in
date format, but I am just using the days of the week as text -
because I want to count the days of the week]
Saturday
Saturday
Wednesday
Saturday
Friday
(etc - each represents the day of the week and article appeared, and a
day of the week may appear more than once)

I know how to produce a little table that will tell me how many
Saturdays, Sundays etc.
Column F Column G
Saturday =countif($B:$B,F2)
Sunday =countif($B:$B,F3)
Monday = countif($B:$B,F4)

Now I want to produce a table that lists for each publication how many
articles appeared for each day of the week.
It will have the publications in the first column and the days of the
week in the next 7 columns

Saturday | Sunday | Monday | etc
The Age 23 24 17 etc
The Courier Mail

I hope this is clear.
Is there some simple formula that I can use?

Regards,
Harry- Hide quoted text -

- Show quoted text -

You could also look at the database functions like DCOUNT() &DCOUNTA()
with criteria as for an advanced Filter on data. I'll Post more when
I've got time but it's worth looking at in help.

Regards

Wingco

 

[Harry Flashman]

Hello Harry
With defined names "Publication" to refer to your data range in column A**,
"DayOfWeek" to refer to your data range in column B**, C2 containing
Publication criteria, D containing Day of week criteria, formula:

=SUMPRODUCT((Publication=C2)*(DayOfWeek=D1))

**Nb: Defined names must refer to same dimension ranges (eg Publication
=A1:A500, DayOfWeek=B1:B500) and NOT entire columns range(eg A:A, B:B),
otherwise SUMPRODUCT function will not work.

You may also consider using pivot tables.

HTH
Cordially
Pascal

I tried the formula exactly as you said but it did not work. The
result for each item was 0.
Then I tried modifying the fomula like this:
=SUMPRODUCT(Publication=$C1)*(DayOfWeek=D$1)

But it still didn't work- still zero
Just to clarify:
Column A = Publication name (publication names appear more than once
in this list)
Column B = Day of the week

Columns A & B consist of over 900 rows.

Column C = unique publication name (43 names)
For examle:
The Age
The Courier Mail
Sydney Morning Herald
etc

Row 1, Columns D to J = Days of the week
For example:
D1=Saturday
E1=Sunday
F1=Monday
etc for each day of the week.

I entered the formula =SUMPRODUCT(Publication=$C1)*(DayOfWeek=D$1)
into cell D2 and dragged it down my list, and then across.
Alas the result was zero in each cell.

However I was able to use countif to count the publications and days
of the week.

Have I followed your instructions properly? Or did I miss something?

Thanks for your help.

 

[papou]

Harry
Missing parenthesis:

=SUMPRODUCT((Publication=$C1)*(DayOfWeek=D$1))

HTH
Cordially
Pascal

"Harry Flashman" <[email protected]> a écrit dans le message de (e-mail address removed)...

Hello Harry
With defined names "Publication" to refer to your data range in column
A**,
"DayOfWeek" to refer to your data range in column B**, C2 containing
Publication criteria, D containing Day of week criteria, formula:

=SUMPRODUCT((Publication=C2)*(DayOfWeek=D1))

**Nb: Defined names must refer to same dimension ranges (eg Publication
=A1:A500, DayOfWeek=B1:B500) and NOT entire columns range(eg A:A, B:B),
otherwise SUMPRODUCT function will not work.

You may also consider using pivot tables.

HTH
Cordially
Pascal

I tried the formula exactly as you said but it did not work. The
result for each item was 0.
Then I tried modifying the fomula like this:
=SUMPRODUCT(Publication=$C1)*(DayOfWeek=D$1)

But it still didn't work- still zero
Just to clarify:
Column A = Publication name (publication names appear more than once
in this list)
Column B = Day of the week

Columns A & B consist of over 900 rows.

Column C = unique publication name (43 names)
For examle:
The Age
The Courier Mail
Sydney Morning Herald
etc

Row 1, Columns D to J = Days of the week
For example:
D1=Saturday
E1=Sunday
F1=Monday
etc for each day of the week.

I entered the formula =SUMPRODUCT(Publication=$C1)*(DayOfWeek=D$1)
into cell D2 and dragged it down my list, and then across.
Alas the result was zero in each cell.

However I was able to use countif to count the publications and days
of the week.

Have I followed your instructions properly? Or did I miss something?

Thanks for your help.

 

[Harry Flashman]

Hello Harry
With defined names "Publication" to refer to your data range in column A**,
"DayOfWeek" to refer to your data range in column B**, C2 containing
Publication criteria, D containing Day of week criteria, formula:

=SUMPRODUCT((Publication=C2)*(DayOfWeek=D1))

**Nb: Defined names must refer to same dimension ranges (eg Publication
=A1:A500, DayOfWeek=B1:B500) and NOT entire columns range(eg A:A, B:B),
otherwise SUMPRODUCT function will not work.

You may also consider using pivot tables.

HTH
Cordially
Pascal

"Harry Flashman" <[email protected]> a écrit dans le message de (e-mail address removed)...

Hi, can anyone help with this please
I have a table that lists articles published over a month.
I have two (relevant) columns:

Column A (Pulications)
The Age
The Age
Sydney Morning Herald
The Age
The Bulletin
The Examiner
(etc - the list runs for hundreds - each represents an article and a
publication may appear more than once)

Column B (Day of the week) [Btw originally the data for this was in
date format, but I am just using the days of the week as text -
because I want to count the days of the week]
Saturday
Saturday
Wednesday
Saturday
Friday
(etc - each represents the day of the week and article appeared, and a
day of the week may appear more than once)

I know how to produce a little table that will tell me how many
Saturdays, Sundays etc.
Column F Column G
Saturday =countif($B:$B,F2)
Sunday =countif($B:$B,F3)
Monday = countif($B:$B,F4)

Now I want to produce a table that lists for each publication how many
articles appeared for each day of the week.
It will have the publications in the first column and the days of the
week in the next 7 columns

Saturday | Sunday | Monday | etc
The Age 23 24 17 etc
The Courier Mail

I hope this is clear.
Is there some simple formula that I can use?

Regards,
Harry- Hide quoted text -

- Show quoted text -

Okay I just experimented with making a pivot table. Now that's a handy
funciton!
It worked perfectly - and quickly. Thank you.
If it is possbible to make SUMPRODUCT work I would still like to
learn.
The same goes for DCOUNT (thank you to Wingco)
I shall have to do some further investigation.
Btw this newsgroup is amazing. It is so great that people are willing
to help simple people like me. I really appreciate it, thank you.

 

[Harry Flashman]

Harry
Missing parenthesis:

=SUMPRODUCT((Publication=$C1)*(DayOfWeek=D$1))

So I did. Thanks for picking that up. The formula works perfectly.
Thanks for the education.

 

[papou]

Harry
See this link for further information about SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Cordially
Pascal

"Harry Flashman" <[email protected]> a écrit dans le message de (e-mail address removed)...

Hello Harry
With defined names "Publication" to refer to your data range in column
A**,
"DayOfWeek" to refer to your data range in column B**, C2 containing
Publication criteria, D containing Day of week criteria, formula:

=SUMPRODUCT((Publication=C2)*(DayOfWeek=D1))

**Nb: Defined names must refer to same dimension ranges (eg Publication
=A1:A500, DayOfWeek=B1:B500) and NOT entire columns range(eg A:A, B:B),
otherwise SUMPRODUCT function will not work.

You may also consider using pivot tables.

HTH
Cordially
Pascal

"Harry Flashman" <[email protected]> a écrit dans le message de (e-mail address removed)...

Hi, can anyone help with this please
I have a table that lists articles published over a month.
I have two (relevant) columns:

Column A (Pulications)
The Age
The Age
Sydney Morning Herald
The Age
The Bulletin
The Examiner
(etc - the list runs for hundreds - each represents an article and a
publication may appear more than once)

Column B (Day of the week) [Btw originally the data for this was in
date format, but I am just using the days of the week as text -
because I want to count the days of the week]
Saturday
Saturday
Wednesday
Saturday
Friday
(etc - each represents the day of the week and article appeared, and a
day of the week may appear more than once)

I know how to produce a little table that will tell me how many
Saturdays, Sundays etc.
Column F Column G
Saturday =countif($B:$B,F2)
Sunday =countif($B:$B,F3)
Monday = countif($B:$B,F4)

Now I want to produce a table that lists for each publication how many
articles appeared for each day of the week.
It will have the publications in the first column and the days of the
week in the next 7 columns

Saturday | Sunday | Monday | etc
The Age 23 24 17 etc
The Courier Mail

I hope this is clear.
Is there some simple formula that I can use?

Regards,
Harry- Hide quoted text -

- Show quoted text -

Okay I just experimented with making a pivot table. Now that's a handy
funciton!
It worked perfectly - and quickly. Thank you.
If it is possbible to make SUMPRODUCT work I would still like to
learn.
The same goes for DCOUNT (thank you to Wingco)
I shall have to do some further investigation.
Btw this newsgroup is amazing. It is so great that people are willing
to help simple people like me. I really appreciate it, thank you.