 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Count items based on two criteria
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 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 > Hi, can anyone help with this please > I have a table that lists articles published over a month. [quoted text clipped - 42 lines] > Regards, > Harry > Hello Harry > With defined names "Publication" to refer to your data range in column A**, [quoted text clipped - 64 lines] > > - 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 > Hello Harry > With defined names "Publication" to refer to your data range in column A**, [quoted text clipped - 12 lines] > 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 Missing parenthesis: =SUMPRODUCT((Publication=$C1)*(DayOfWeek=D$1)) HTH Cordially Pascal On Oct 8, 6:02 pm, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote: > Hello Harry > With defined names "Publication" to refer to your data range in column [quoted text clipped - 13 lines] > 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 > Missing parenthesis: > > =SUMPRODUCT((Publication=$C1)*(DayOfWeek=D$1)) So I did. Thanks for picking that up. The formula works perfectly. Thanks for the education. > Hello Harry > With defined names "Publication" to refer to your data range in column A**, [quoted text clipped - 64 lines] > > - 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 See this link for further information about SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html HTH Cordially Pascal On Oct 8, 6:02 pm, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote: > Hello Harry > With defined names "Publication" to refer to your data range in column [quoted text clipped - 65 lines] > > - 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.