Solved

count records

Posted on 2012-03-29
4
240 Views
Last Modified: 2012-03-29
Hi I am making a call log, I am a manager at a resort and I want to report on my calls after hours.

In ms excel, I have manually entered data for the following fields  
Day, date, time,  Hr, Call No.

I have attached my spreadsheet with calls for the last 15 days of march now I Need the totals for the month.

I have put Question marks in the fields I need to populate

So, total DAYS to start with. If i have entered a day in the Day field I want to count it and put it in a box in the TOTAL DAYS  at the bottom. THe answer should be 17.

Days on are my days on - see comment in red. the rest is straight forward!

This is pretty basic for you guys Im sure. I'm use to Mysql, Im trying to relate the functions but not having much luck.. Could someone fix this for me and send it back, so I can see how the functions work.. Thank VERY much !!
Experts-excel-help.xls
0
Comment
Question by:kingjely
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37785511
As requested, your formulas are populated in the example:

This caters to Fri,Sat, or Friday,Saturday as you've entered in your sheet.  You might consider a data validation list so you force the user to remain consistent in data entry.

The key formula (most sophisticated) is for days on, like this:

=B30-SUM(COUNTIF($B$2:$B$27,{"Fri","Sat","Friday","Saturday"}))

or:

=B30-SUMPRODUCT(($B$2:$B$27={"Fri","Sat","Friday","Saturday"})*1)

Cheers,

Dave
Experts-excel-help.xls
0
 
LVL 8

Author Comment

by:kingjely
ID: 37785521
THanks Dave!

=COUNTA(B2:B27) to break this down so i understand

COUNTA is the field name?
B2:B27 means field B2 to B27?



=COUNTIF($F$2:$F$27,">0")

COUNTIF is this a function?

what is '$'

I understand your saying count the fields F2 to F27 if the field is greater then 0
Could you just explain what each thing is so I understand the basics! Thanks and you get my points
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37785529
>>COUNTA is the field name?

COUNTA is an Excel worksheet function.  You can hit F1 for help and search for COUNTA, but it basically counts nonblank cells, so any data entered in a cell gets counted.

>>B2:B27 means field B2 to B27?

B2:B27 means RANGE B2 to B27

------------------

>>=COUNTIF($F$2:$F$27,">0")

COUNTIF is a function (see help again as a starting point)

The first parameter is a RANGE, the second is a CRITERIA.  So in the RANGE F2:F27, it counts all values > 0 (so it counts all numeric entries > 0 and blanks resolve to zero in thsi function).

----------------------

$ around the range parameters means ABSOLUTE ADDRESSING.  Without means RELATIVE ADDRESSING.

If you had a formula, e.g., SUM(A1:A1000) and copied it to the right, the formula would become =SUM(B1:B1000).

If you copied it down one row, the formula would be SUM(A2:A1001).

However, if you LOCK the column, then the column part of the range wouldn't change - e.g.,

=SUM($A1:$A1000) copied to the right would be the same formula

=SUM(A$1:A$1000) copied to the right would transform to =SUM(B$1:B$1000)

If you copied either down, it would still range from 1:1000.

HTH

Dave
0
 
LVL 8

Author Closing Comment

by:kingjely
ID: 37785536
THanks I'll look into the functions.
I am master Mysql and databasing but horribly new to any functions in excel.. THanks for the pointers ;)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question