# If Then Formula That Includes Counting?

Dec 20, 2013
I am trying to creat a summary sheet to an attendance log that goes back several years. Idealy, I want to be able to set a date range in the formula that will show me a count of how many absenses, lates, or leave earlies. The table I am taking the data from has columns from left to right (Date, Absense, # of Absenses, Late, Duration of, Left Early, Duration of, and MP) For example in column A I want to see the date Range of 10/1/2013 to 4/1/2014 and I want a count/sum of each of the categories.

View 5 Replies
ADVERTISEMENT
Jan 30, 2014

What I need is a formula for the following:

If Column A equals "No Show" and Column B equals "Anthem" then sum of cell C2 divided by D2

Every time I input this simple little formula I get an error.

A

B

C

D

Status

Coverage

Counts

No Shows

No Show

Anthem

5

10

View 1 Replies
View Related
May 1, 2014

I have a macro that will create 15 reports based on a filter in a Pivot Table and will save them to a folder based on the filter names. This work great, however in one of the reports I have a few vlookups as an example of one of them,

Code:

=VLOOKUP(Selector,Trends2!$B$6:$LV$22,AI50,FALSE)

. Now when the report is created from the macro, the vlookup is changed to,

Code:

=VLOOKUP(Selector,'Z:ISB DataISR RASC non transactional dataISR dataNational and Zone Reports[ISR National and Zone Template v18a.xlsm]Trends2'!$B$6:$LV$22,AI50,FALSE)

Is there any way to stop this from happening? Is it a general thing in Excel? Code below that creates the reports if needed

Code:

Sub Create_National_Report()

'Start of report creation process

Sheets("Pivot Tables (2)").Select

[Code]....

View 1 Replies
View Related
Jun 25, 2007

I have seen a few posts that are close to this but not quite right. I have a situation where I need to do "=LEN..." formula that counts trailing zeros at the end of numbers (meaning zeros that are displayed at the end of a number but do not appear in the actual cell value).

example:

Cell E19 contains the actual value 4773.52, but it is displayed in currency format with FOUR digits so it displays as $4,773.5200.

The formula I am using is =LEN(MID(E19,FIND(".",E19)+1,4)). The result is 2, which means it does not include the last two trailing zeros in the LEN count.

Is there anyway to get it to include the trailing zeros so the result equals 4? Either w/formula, macro, or UDF?

Is weird I know, but I need to know how many decimal places (displayed, not actual) for many numbers on many sheets that I do not create myself.

View 9 Replies
View Related
Jun 14, 2006

I have a document that I created that has merged cells. In order to autofit the rows of this document, I referenced the merged cells in singular cells that are not visible to the users of the document. (They are not hidden)

It had been working relatively nicely, but now the autofit function is not working correctly. If I copy and paste values into the cell it will work, but this would not be my ideal solution. I have tried playing with the number format, which works for the most part if I set it to "&" - text. It doesn't work for some longer cells with line breaks in them.

View 3 Replies
View Related
Jun 24, 2011

Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.

I am using Excel 2010.

View 14 Replies
View Related
Feb 3, 2014

I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls

View 1 Replies
View Related
Dec 3, 2008

Please see attached file. In my column L, I'm trying to find a formula that would reference my column J to the table in the data tab. My problem is the data in column J has a hyphen (-) in the number ex.. 047-258-1 and the next tab does not have a hyphen in between the number...ex 0472581. The correct results in cell L3 would be Walmart. How can I adjust my formula to accommodate this hyphen. Can this also be reflected in my pivot table as well?

View 6 Replies
View Related
Jun 12, 2009

What would the formula be if A1 includes a certain string of text, then B1 returns a given value? e.g., if A1 contains baby then B1=carly.jpg?

View 3 Replies
View Related
Jun 10, 2014

I have a list of codes in a column, they are all 6 digits long. Some are 3 letters followed by 3 numbers (eg. ABC001), and some are just 6 numbers (eg. 000123).

Example list....

ABC001

ABC002

CDE003

EFR005

000123

000345

004567

000567

000789

001234

002345

BBB002

CCC003

DDD004

I want to be able to autofilter with the criteria being all the ones that include letters, or all the ones that are only numbers. So I need to fill a variable with each list so that I can use it for the autofilter criteria.

How would I go about identifying the ones that have letters in from the ones that do not?

View 9 Replies
View Related
Sep 18, 2009

I'm writing some VB in an Excel macro to search and replace a string of text.

The problem is that the string contains the " symbol, so when Access gets to that symbol it thinks that it's reached the end of the string.

e.g.

I want to replace "0519","863027" (including the quotes) with "0001","863027"

This is what the code looks like, but it obviously doesn't work :

View 11 Replies
View Related
Jan 16, 2014

I am having trouble sorting the data in a worksheet that includes a column of email addresses. Every time I try to sort the sheet it excludes the email column.

View 2 Replies
View Related
Nov 24, 2008

I'm looking for a function similar to =WORKDAY but with a slight variation.

I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.

For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).

View 9 Replies
View Related
Jan 14, 2014

I have a workbook that we are using to track some training, the spreadsheet has the date of the course at the top and the trainee's name in column A

Additional sheets will be added each time a class is scheduled labeled with the date of the class and in the same format as the ones included titled: "1.13.14" & "1.20.14".

I need to add a table to the workbook that will collect ever new hire from column A and the Date of their class from cell "B1" in each sheet.

Copy of SLC-BD Peer Training 1.xlsx

View 4 Replies
View Related
Jan 12, 2012

I'm creating a restaurant rota spreadsheet and I need to calculate the hours for each waiter, which is easy enough. But I've got to include stand-by shifts and cover shifts into the rota for the staff and I'd like the formula to ignore the "standby" text, etc in its calculations. I know you can use the =IF(... function, but that only works with one value. As well, the standby shifts will change from shift to shift and week to week, so I need one formula that might encompass all these needs... if there is one!!

An example of my rota for you to see:

MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY

Total hours for week START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH

10:00 17:00

10:00 17:00 12:00 16:00

Lunch Hrs/Wk 18:00 #VALUE! 19:00 24:00

standby standby 19:00 24:00 standby standby o2 Cover o2 Cover

Dinner Hrs/Wk #VALUE!

View 5 Replies
View Related
Nov 3, 2009

I have a worksheet that includes a cell that gives the current week of the month, in the form of "Week #". I've been using the formula below for several months and have had no issues until this month, and it's driving me crazy.

Given that cell D2 is the beginning of the week (usually Monday, but at the first of the month it may be a different day):

="Week " & ROUNDUP((DAY($D$2)+WEEKDAY(DATE(YEAR($D$2),MONTH($D$2),0)))/7,0)

I'm not sure where the logic is going wrong here; it's worked so well up until now. For the week beginning November 2, 2009, the formula above returns "Week 2" rather than "Week 1".

View 9 Replies
View Related
Jul 2, 2007

Does anyone have a comprehensive / detailed guide on using the Solver which includes many examples?

View 3 Replies
View Related
Mar 6, 2014

I need to count the # of unique employee #s based on the criteria in column B. If the value in column B=0 then I do not want to count the employee #.

I realize a pivot table would be much easier but I need to show each line which includes other data.

View 6 Replies
View Related
Nov 7, 2007

I am trying to open excel files in a file path which includes folders which also have excel folders i wish to open there are quite a few.

At the moment i am working with this code but it fails to open excel files which are within the folders in the specified file path. Its fine for excel workbooks in the folder specified by file path.

This is the code

Sub RunCodeOnAllXLSFiles()

Dim lCount As Long

Dim wbResults As Workbook

[Code].....

View 9 Replies
View Related
Jun 27, 2014

I am working with a very large spreadsheet 10k references... I need to add sequential numbers in a column to identify the references but I need to use he filter in the author column due to the way my referencing software exports the data...

When I try to use the pull down autofill it just keeps repeating the last or second last number of the cell - the autofil box that usually appears has disappeared.

Im using excel 2010

View 2 Replies
View Related
Aug 6, 2008

When I set a "for each cell in range" to go through the range, and if there are merged cells in this range, this will result an endless loop.

View 9 Replies
View Related
Jan 19, 2006

how can you custom a cell format in which you may in type in information that is displayed as ###-##-####-@# I searched everywhere and cannot find this.

For example, a medicare number.

I would need to type in 581566223b6, and it should be displayed as 581-56-2236-B6.

View 9 Replies
View Related
Nov 20, 2008

How do I get cell C4 to give me a total of the times a score in column C is > than opposing score next to it in coumn D? Also, a total in cell D4 to show the number of times this team's score was less than the opposing score in column C. This would be for a running Win/Loss record.

View 3 Replies
View Related
Nov 11, 2008

I need to count how often a formula is true over 2 columns and have that total in a 3rd column. Here's my problem:

A B

3 2

4 5

2 1

1 6

4 5

Every time A is greater than its corresponding B, i need to count 1 and have the total show up in a certain cell on my spreadsheet. I can't seem to get the A>B portion into my formula and have it hold true down the entire column and count it all up in 1 cell.

View 3 Replies
View Related
Nov 15, 2006

I have the following formula

=IF(B8>='Credit Details'!$C$12,IF(SUM($D$8:D8)>=$I$2,"TRUE","FALSE"),"FALSE")

What this does is the date in referenece 'Credit Details'!$C$12

Is equal or greater then Reference B8 then complete the main formula

(SUM($D$8:D8)>=$I$2,"TRUE","FALSE")

But the problem being the formula always counts from D8 but I need it

to start counting from what the date is B is => then 'Credit Details'!$C$12

Is there anything you can think of?

Example, if I want the formula to count all the numbers in row D after the date is above 01/11/06 and the date is in row B. Before I counted $D$8:D8 so every time the formula repeating it always started from D8

But the date might not go above 01/11/06 til cell D20 but changes each time

View 9 Replies
View Related
Aug 11, 2006

I am trying to figure out a formula that will simply just count up the rows remaining in my spreadsheet after a filter. For example, I have a spreadsheet with 1,000 rows in it, and I filter out one section, the numbering does not adjust and I now don't know how many rows are showing.

View 9 Replies
View Related
Jun 6, 2014

I have an export spreadsheet which details information from column a to column p which i need ...(supplier code...net value..vat...comments..etc)

However there are also subheaders, sub totals titles which take up entire rows and contains data i do not need.

Is there a way of telling excel to choose certain rows (eg ...A19 to P19, A20, to P20, A45 to P45 )

depending on if the first cell of the rows i need eg Cell A19,A20, A45 etc contains a supplier code such as from the following list :

AC01NET

AD05

210

003

BL01

KF01

KF01

CA06

I would need to paste the selected rows into another sheet

View 11 Replies
View Related
Feb 11, 2014

i have a spreadsheet that acquires data from 42 other worksheet and in a row of cells it has yes or no at the bottom of that row i have =COUNTIF(A2:A34,"YES"). But it will not count the yes' because the cells that it is trying to count all have formulas in them like =tues!A34 or similar is there a different formula i can use to still count the yes' even in a cell containing a formula?

View 7 Replies
View Related
Oct 10, 2008

Is there any formula that will count the space of a cell. For Example. If I type Mr. Ashish Patel in a cell the formula should give me count as 2 as there are 2 spaces in this name One after Mr. and another after Ashish.

View 2 Replies
View Related
Dec 23, 2008

I have a spreadsheet which matches Reference numbers in Column A against Column B and vice versa; and assigns unique or duplicate values. I would like to be able to count the number of duplicates and uniques giving a total of how many appear in the spreadsheet. I've attached the spreadsheet to give you an idea of what its like.

View 3 Replies
View Related