Calculating Column Based on 2 Other Columns

[colugino]

I apologize if this is a repeated question. Have been searching and not finding exactly what I need.

I have a spreadsheet that needs to calculate numbers based on two conditions. To give a bit more detail I am trying to calculate chat volume for our office based on the day and hour for an entire month. To complicate it more, I have a sheet with our call log that counts how many chats came in during that hour per day. So I need to know the total number of chats that came in on all Mondays in the month at 8am, for example.

So in column A I have Sun through Sat, in column B I have "12:00 AM", "1:00 AM, 2:00 AM, etc... In column C I have the number of calls for that day at that hour.

Where the formulas need to go is another workbook that will be doing the calculations. What I need a formula for is to say "If on sheet 'Call Data' if any cell in column A shows "Mon" and a corrosponding cell on the same row shows "8:00 AM" to add the number that is in column C.

The end result that I need is to see:

Mondays at 8:00 AM we have had a total of 27 calls.

I have attached the data sheet. I have highlighted the cells that I need to calculate. The second sheet is where I need to enter the formulas.

[NBVC]

Firstly, you should make both Column A's in both sheets consistent... either both have full day name or abbreviated day name...

then in C2, try:

=SUMPRODUCT(('Chat Data'!$A$2:$A$287=A2)*('Chat Data'!$B$2:$B$287=B2),'Chat Data'!$C$2:$C$287)

copied down.

[herbds7]

Excel 2007 PivotTable
No formulas or code used.
With charts.
http://www.mediafire.com/file/1ezmlw5moz3/Chats.xlsx

[colugino]

Thanks for the help! I tried the first one and for some reason it did not work (then my computer crashed and I lost all my data!). The pivot table looks like what I need. Thank you again!!

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save