Counting quantities for a given week

paulslusser

New Member
Joined
Jan 16, 2009
Messages
7
In cells A4:A250 i have dates entered
A4=1/2/09
A5=1/2/09
A6=1/14/09
A7=1/15/09
A8=1/3/09
A9=1/10/09
A10=1/16/09
A11=1/19/09
A12=1/21/09

Dates are entered all the way through to A250 in no particular order but only for that month. In this example January and no dates for Sunday.

D4=Yes
D5=Yes
D6=Yes
D7=No
D8=No
D9=Yes
D10=No

In Collumn A... The Date is entered every time a call is made
In column D... Yes and No is entered depending if a client was reached with that phone call.

Calls are not made on Sunday

I need to be able to track 2 different things from this data in 2 separate cells...

:confused: 1. Count how many total calls were placed each day for a given month
(How many calls were made Mon 1/19/09?)
(Now many Calls were made Tues 1/20/09?)
and so on through that given month for each day in Jan except Sundays

:confused: 2.Count how many calls of those calls for each day were reached? (D4=Yes)

What are the equations that can do this and will they be easily <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 10"><meta name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cpaul%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C07%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]-->duplicable for Feb and March and so on?

Thank you for all of your help.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
=COUNTIF(A:A,DATEVALUE("1/5/2008") will return the number of calls made on Jan. 5 '08

=SUMPRODUCT(--(A1:A250=DATEVALUE("1/5/2008"),--(D1:D250="Yes")) will return the number of Yes's for that same date.
 

paulslusser

New Member
Joined
Jan 16, 2009
Messages
7
=COUNTIF(A:A,DATEVALUE("1/5/2008") will return the number of calls made on Jan. 5 '08

=SUMPRODUCT(--(A1:A250=DATEVALUE("1/5/2008"),--(D1:D250="Yes")) will return the number of Yes's for that same date.
Is there another way to have this equation without the "datevalue"? I will need to copy this equation for everyday of the year if I use the datevalue.

and by the way...
That is amazing how quickly you were able to come up with a starting solution. Thank You...
 

paulslusser

New Member
Joined
Jan 16, 2009
Messages
7
=COUNTIF(A:A,DATEVALUE("1/5/2008") will return the number of calls made on Jan. 5 '08

=SUMPRODUCT(--(A1:A250=DATEVALUE("1/5/2008"),--(D1:D250="Yes")) will return the number of Yes's for that same date.
Is there another way to have this equation without the "datevalue"? I will need to copy this equation for everyday of the year if I use the datevalue.

and by the way...
That is amazing how quickly you were able to come up with a starting solution. Thank You...
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
Anything that returns an Excel serial date.

=COUNTIF(A:A,39452) would work.
 

paulslusser

New Member
Joined
Jan 16, 2009
Messages
7
I worked out the first suggestion and actually it worked the best with another calculation i had already generated.

So thank you so much.
 

paulslusser

New Member
Joined
Jan 16, 2009
Messages
7
The first suggestion was accidentally missing a ")" and after i added that it worked. Additionally by having a separate cell for each day of the month I was able to run a separate percentage calculation for each day. I do have to change the "datevalue" for each day but it works.
 

Forum statistics

Threads
1,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top