1. N

    Excel Formula with Condition

    Hi Team, I am in a bit of a fix with a formula I want to set up. Goal is to calculate the total meal allowance (column E). The intended formula is: Meal allowance rate (column B) * days in Germany (column C) * ((no of children)+1 if the number of days in Germany is more than zero). I am...
  2. D

    Page numbering in Excel with formula

    I need a solution in Excel, i want to make an indexing page (a sort of content page / summary with page numbering) i want to use a formula what will search for a specific word eg. (OEM A & OEM B) and will make a summary page as i attached in the picture.
  3. A

    Excel to return a value in a column, if multiple criteria meets

    I tried to return a value in sheet1, column A (vertical), to sheet 2 (Horizontal format), if the "Process_Step" and "WO" is matched. As the data is huge and there're many columns, I do not know how to specifically to use the index and match or vlookup, to return the column A value (Date) if the...
  4. P

    First letter of first word and full last word in a cell

    I have a spreadsheet which is a dump of our user's full names from an old email server. The names appear to be in the formats Firstname Surname, Firstname1-Firstname2 Surname, or Firstname Surname1-Surname2 depending on whether they have a hyphenated firstname or two surnames hyphenated. I'm...
  5. A


    Hi All, I have range of cells B21 to N22. I cell B21 is a value of 8 and B22 a value of 9, C21 a value of 7 and C22 a value of 5. What I'd like is to have is a formula that will sum up the below. I have just used nested If statements ( I know this is not what I'm after, but more so, to give...
  6. E

    Help with excel formula

    Hi all, I am having issues with the following formula: =COUNTIFS('Raw Data - Travel'!D2:D350,">"&'Total Summary Data - Travel'!B3,'Raw Data - Travel'!D2:D350,"<"&'Total Summary Data - Travel'!C3) Cells B3 & C3 are start and end dates. D2:D350 contains dates. The formula works perfectly...
  7. S

    vba to pick latest date and show overdue post 3days

    I want to write a VBA code which picks the latest date from column remarks and if its been more than 3 days if column remarks isnt updated with new date it should show overdue and if RFI status is set to closed Follow up overdue should automatically be turned to No
  8. D

    Extract text between MULTIPLE parentheses in one cell

    Hello, I would like to extract text between MULTIPLE parentheses in one cell (without using VBA). The blue text in the picture below is what I'd like to achieve automatically. When there is just one set of parentheses, it's easy. For B1 I could use...
  9. V

    how to add one in JK-1050 so that it will be JK-1051, if some condition meet

    Hi All, Thanks in advance, we have the following sheet Sheet1 Sheet2 If Sheet2!c2 is match with Sheet1!B2 then in Sheet1!B3 JK-1051 else blank, required a arrayformula if possible
  10. L

    How do I make one cell always equal to a specific number while still taking into account of other cells.

    I need a formula which allows the 'Money Leftover' cell K7 to always equal 500. I've probably worded the above really vaguely, however I'll add an image to try and get explain myself. What I need: As I start to add in values under columns C-I, the values in K will either go up or down...
  11. K

    Display on pivot table a count of only those column values which has a unique ID (in a separate column)

    Explanation of my source table: I have column E which has data-validation dropdowns for Reviewer numbers, and column R which has traffic transaction details. Reviewer now check each of the transaction (Column R), and once review is done, will click on the dropdown (Column E) and select Reviewer#...
  12. S

    Highlight entry based on list

    I have a list of 5,000 contacts created for an email blast that includes email address, first and last name, and company name. I have a separate list of company names I do not want to receive an email. I want to create a formula (or conditional formatting?) that would compare the 2 lists and...
  13. B

    The smallest range with the highest number of data

    I have some data like this: DATA 1 1 2 5 5 9 11 14 14 18 18 29 29 30 37 38 40 40 61 62 2 2 4 4 6 9 9 10 12 15 16 17 30 31 35 40 41 45 50 60 1 4 5 5 9 9 10 10 11 25 25 28 30 35 45 50 51 57 60 68 The values are up to 100. No bigger values. Each...
  14. V

    Arrayformula with time delay

    HI All, We have the following sheet Head 1Head 2116004265606-07-2020 13:060.00149306212065265606-07-2020 13:080.0056713124004265606-07-2020 13:16 required a formula who can do the calculation in A column. A3=C3-C2 A4=C4-C3 have a lots of date in the cell so need a array formula and you can...
  15. V

    if or filter or any other formula/code

    Hi All, Thanks in advance, we have the following sheet Item CodeWork 1Work 2Work 3Work 4P-1P-2P-3P-1P-2P-3P-1P-2P-3P-1P-2P-3A-405fsxvxvA-820fdsfdfdfokvxvxA-440fdsffdsokvxvxfsdssfokfdafA-650vxvvxvokxcvvgsdsokvcxxvvxcokgdgA-260cxzccxok vcxvxvxcok cvvvxcvokdgdggdgdgok and want to do following...
  16. S

    Tricky budget sheet code

    Hi, I have created a custom budget sheet that we will be using in our organisation and have hit a road block with what I would like it to do next, and hope that someone may be able to offer some help. I have scoured the internet and tried to work this out but my knowledge is limited! Thank you...
  17. V

    if with some condition

    Hi all, we have the following sheet, and want to change the color if the condition meet according to E MAX LEVELQuantityExtra15001600Normal50035060 - 40% balance20071BELOW 30%10025 help pls
  18. E

    Split results of query array, apply different formulas to each then send to Worksheet?

    I have a macro that uses a query to open a tab delimited text file and import some (but not all) of the columns into a specific worksheet in the same workbook from where the macro was called. This works perfectly except I need to run the data from each column through a formula that cleans up the...
  19. B

    Need a formula - filter so 2 columns totalling more than or close to another column only show

    Column I in my excel file shows unpaid invoices. Column K shows uninvoiced items. Column P shows the customer's balance on account . Most of the time, P should be greater than I+K. However we want to be able to easily spot the customer files that are close to or have exceeded the customer's...
  20. D

    MIN&MAX works for 18/19 but not 19/20

    I have a spreadsheet that for each item calculates the number of days that fall within the financial year and then the cost that would relate to it. The formula is: =I10*((MAX(MIN($Q$1,H10)-MAX($P$1,G10)+1,0))/365) Where:- P1: first date in the current financial year being reported -...

Some videos you may like

This Week's Hot Topics