# calculating

1. ### Sumifs

I have used a SUMIFS function in workbook A to look up a value from a different workbook (workbook B) based on a three criteria. The formula works perfectly as long as the other workbook (B) is opened. When I close workbook A and reopen it with out opening workbook B, I get a "VALUE" until I...
2. ### Calculating an active cell range

Hi, I have a spreadsheet that calculates a range on sheet2 for me, but it also calculates sheet1. I just want sheet 2 calculated and the data table on sheet1 to not calculate. Here is the code I'm using (I recorded a macro to get this) right now Excel is set to manual calculate. I've...
3. ### help with if formula

Hi i have 2 dates and using the below formula due to different months this is not calculating correctly <tbody> 30/11/2018 21:10 03/12/2018 08:00 Late </tbody> =IF(I3< l3,"ok","late") <l3,"ok","late") <l3,"ok","late")[code]<l3,"ok","late")[ Can Anyone Help...
4. ### Countif Not Calculating Properly

Hello, I have a data set that is very large (180k rows). I am using a countif function to see which ID#s are showing up twice. I do not want these removed, as I still need them in the data. However, when I use the countif function, it does not show multiple counts for ID#s that I know are...
5. ### Sumproduct calculation

I have a table i would like to sum the total for each month of the year, however, I'm not sure why is not calculating the sum. Can someone assist. The array formula I'm using... =SUMPRODUCT(MONTH(D16:D45)=T3,YEAR(D16:D45)=U2,P16:P45) Thank you
6. ### Calculating Time deficit

I am trying to calculate the difference between times but they are not calculating properly, I am sure it is a format issue but I can not find out what. Cell Z17 has 44:00 in it and I want to calculate 44-Z17 to show 00:00 in cell AA17 but it shows 04:00 instead. if a cell Z18 has 42:00 then...
7. ### Calculating Time

When adding the Hours Worked for each it is not calculating properly, 12:00 + 10:00 + 10:00 + 12:00 is adding up as 22:00 instead of 44:00. Can anyone help with this? Shazz x
8. ### Calculating Average Percentage Between Multiple Worksheets

While calculating average percentages amongst multiple worksheets is fairly straightforward, the premise seems to be that the data being calculated must be in the same cell on each worksheet, at least, that's what I have gathered from perusing different forums. I have four worksheets; each...
9. ### CSE Array with 38,000 lines - 24-hours later it’s still calculating

I have a spreadsheet A - AZ, 38,000 lines. Seeking matches for 5,000 in the 38,000 lines. Often I work up to 65,000 lines A - AZ but always multiple lookups. I’ve never tried Index - Match on this size file. The actual formula looks at six ‘conditions to match’ to return a match. The...
10. ### VBA: This works but is there a cleaner way?

Private Sub Worksheet_Change(ByVal Target As Range) Range("e3:e522").Calculate ThisWorkbook.Sheets("Multi Build").Calculate Range("am3:am100").Calculate ThisWorkbook.Sheets("Distro").Calculate End Sub I'm calculating 1 sheet when changes are made in a range and I am also calculating another...
11. ### COUNTIF - Formula Not Calculating Correctly...

I have no idea why the below formula is not working. Numbers in column A are formatted correctly as numbers. This formula is in cell B2 and copied down to cell B6: =COUNTIF(A\$2:A\$6,"<"&A2) An incorrect result is given in cell B5. I manually typed the results I would expect in column C. Any...
12. ### SUMIFS and SUMPRODUCT formula not calculating all values

I have a report that should calculate won sales of reps in our company based on the date the contract was processed is where the contract totals should appear in the report for the entire calendar year 2018. Based on my data for a particular rep, the total amount is supposed to be \$14,765...
13. ### Calculating hours of late

Looking for assistance in calculating how many hours late. <tbody> A B C IN OUT LATE 8:16 AM 5:00 AM L1 </tbody> In C, I would like the remarks L1 if late between 8:16 AM to 9:00AM, L2 if 9:01 AM to 10:00AM, L3 if 10:01 AM to 11AM and so on. I would like any help so each time I will...
14. ### Calculating Time- Formula Error?

I am trying to do a simple timesheet calculating hours worked: 10:35a-5:15p Formula: b1-a1 A1 and B1 are formatted as time with hours, minutes C1 is formatted as time in custom: h:mm Results are- #Value !
15. ### calculates incorrect percentage

Hello, i have a long formula, that displays the number and percentage in the same cell, however currently it is calculating the % incorrectly...
16. ### Making a Formula - Newbie Question

Hi, this is a fairly newbie question, and my first post on here, so bare with me here! I'm currently taking a course on Excel for beginners, and I'm currently stumped on a problem. My assignment has us calculating insurance/doctor's visits with a copayment of \$25.00 and this specific problem...
17. ### calculating tonnes

Hi I am using a table and need help calculating a tonnage =([@[Time OUT]]-[@[Time IN]])/[@[Tonnes Processed]] Time IN = 12:00:00 AM Time OUT = 12:20:00 AM Tonnage = 24 Processing time = 20 minutes therefore the answer needs to be 72 tonnes per hour I rarely use tables but how do i get the...
18. ### Splash Screen while calculating?

I am currently working a workbook that pulls data from a variety of sources. I am using a lot of lookups, which is slowing it down. That is a fix for another time. What I want is for while it is calculating processors, to have a splash screen. I have one currently, but it pulls up after the...
19. ### Days between peaks

Hi, I'm seeking a solution for conditionally counting the days between max values. In the example below, I'm trying to construct a conditional formula for the "DaysUnderPeak" column. This column would output the text "new peak" if the "Peak"(B) value is higher than the previous Date's(A) peak...
20. ### Calculating between 2 dates

Hi guys, I have this spreadsheet. I need to be able to work out how many days holiday each candidate has accrued based on todays date. For example. Based on the amount of days worked per week and how long the contract is for, CAN-10367 will get 30 days holiday for the duration of his...