At work, I created a single-page sp 2

[lefty78312]

At work, I created a single-page spreadsheet with a sumproduct formula to count data that meets certain criteria and how many sales result from the data that meets the criteria. I also notate the time of each data occurrence. Now I just need to break the relevant data into time periods, arbitrarily every 2 hours, with the ability to change it to other time periods; say, 4 hours, if need be.

In other words, I want to be able to track how many occurrences of each type of event occur within a certain time period, e.g., 8:00-10:00, 10:01-12:00, 12:01-2:00, etc., and how many sales occur during that same period. I can then easily track my closing percentage by time period. BTW, since I do the entire month on one spreadsheet, the times are not in chronological order.

I tried a Pivot Table, but I don't understand it well-enough to do it properly. I'm sure a modified sumproduct formula would do the trick; I just don't know how to formulate the time periods so Excel will know what I'm asking. All I end up with is error messages.

Thank you in advance.

 

[lefty78312]

Yogi,

Thank you. I wish I knew how to upload a spreadsheet, which would make everything much easier to explain. Your above spreadsheet seems like it's on the right track. Here is a very truncated sample spreadsheet.

Criteria | Time | Name | Phone# | # of Sales

criteria1 8:03 AM Joe Schmo 312-402-5610 1
criteria2 8:37 AM Willy Nilly 972-888-5555
criteria3 8:53 AM Bob Lob 404-222-3333 3
criteria2 9:27 AM Lucy Goosey 765-544-9632
criteria2 10:14 AM Stan Hand 925-444-6666 2

I already have a sumproduct formula that counts the number of calls and sales by criteria; I just need to further break it down by time period (e.g., 9:01-11:00 AM, 11:01-1:00 PM, etc.). I'm not averse to dumping sumproduct if something else works better. The goal, of course, is to find out what times of a given day are the busiest and the most productive, which could be helpful to management and representatives in many fields.

 

[yogia]

Hi lefty78312:

Let me see if I have caught your intent correctly ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[lefty78312]

Yogi, you're a master! The final piece of the puzzle is the criteria. Regardless of the number of calls I receive, they all fit into 1 of 4 criteria. I'm currently using countif to count the calls by criteria (=countif(a3:a450,"criteria1") for each criteria. If I can also break those down by time period, I'll be there. I can then write a sumproduct formula to give me the data I'm looking for.

I'm just looking for a auto-updating formula for the month. Daily numbers are not necessary.

 

[yogia]

... The final piece of the puzzle is the criteria ...

Would you care to explain what you mean by this -- and provide some sample data along with your expected result ... and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[lefty78312]

Would you care to explain what you mean by this -- and provide some sample data along with your expected result

Again, thank you so much for your assistance with what must be a trivial matter to yourself.

I don't know how to post a spreadsheet, so let me send you a link to a practice spreadsheet:

http://images30.fotki.com/v44/fileNeLY/70c16/1/1162763/5190139/Practice.jpg

Important: I have renamed sheet1 'Info'. The relevant columns are Column A, which contains the criteria, Column B the time, and column G the number of sales. BTW, all of the counting formulae are on another sheet in the same workbook called 'Stats'.

The first formula, which breaks the calls down by criteria, is =countif(info!a3:a450,"criteria1"), etc. The 2nd formula, which counts the number of sales by criteria, is =sumproduct((info!a3:a450="criteria1")*(info!g3:g450)), etc. In a nutshell, I'm trying to add column B, the time, to the formula and break down both the calls (column A) and the sales (column G) by time period.

 

[yogia]

Hi lefty78312:

In the following I have a spreadsheet simulation of the image that you refered to in your last post ...

It is not clear to me what you are asking for. Please refer to the image that I have posted and refering to the cells of the spreeadsheet describe clearly what you are trying to do and what are your expected results along with an explanation, if so necessary, as to why that is the correct result.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[lefty78312]

Thank you, Yogi. The first 2/3 of the equation is already in place. Your above spreadsheet starts the info on column B, but in actuality, everything begins in column A, one column to the left. The goal is to summarize my activity (countif of column A) and productivity (sales, column G) by criteria and time period.

I've highlighted the relevant totals on the 'Stats' sheet of the workbook, viewable at the following URL:

http://images30.fotki.com/v1037/fileVvmL/28d1f/1/1162763/5190139/Practice1.jpg

The stats summarize my month-to-date totals. I want to additionally break them down into 2-hour segments. Daily totals would be nice, but are unnecessary.

 

[lefty78312]

Okay, I gave up on part of my original idea, and I have another 1/3 of it figured out. I just need the final piece of the puzzle.

I now have a formula that will count my calls by time period. The formula is:
=sumproduct(--hour(b3:b400)>=8),--(hour(b3:b400)<10)), etc., where column B contains the time. It will only count up to row 400. Whenever I try to include any row above 400, I get a #VALUE! error message. But never mind that. I take 450 - 475 calls a month; I guess I can live without the last 50 or so. Although it would be interesting to find out why the formula won't work above row 400.

Anyway, how can I also sum the total of sales by time period, which are in column G?