# if()

1. ### Subtotal doesn't work properly

I've used subtotal a lot and never has it failed me. However, now I am lost. Here's my data-set (simplified for example): <tbody> With Formula Flat Data Value 10.000 10.000 Value 10.000 10.000 Result 20.000 Value 10.000 10.000 Value 10.000 10.000 Value 10.000 10.000 Result...
2. ### Can you use a filter in an IF()

Can you use a selection in a filter for a pivot table ie ALL and use an if() to do a calculation depending on filter value?
3. ### IF A1= X and B1=B then add C1 to Z5

Hi, I want to create a formula similar to above. For example, if (A1) country = UK, and (B1) accomm = Hotel, then (C1) expenditure (£10) is added to (Z5) total expenditure. This will be used multiple times. Thank you!! D
4. ### Combining If Statment (Date) with ROUND function

I am attempting to write a formula to assist with quality control during a payroll process (less manual manipulation). I would like a formula that pays an employee a specific amount (let's say \$5,000 + 3%) until a specific date (let's say 11/14/2018). Once that date has past, I would like the...
5. ### IF formula for depreciation

I am working on a project for calculating depreciation and having trouble changing my formula for depreciation expense to an IF formula. I understand that the test should be for book value > salvage value so depreciation is allowed. I am unsure as to how to put all of this into a working...
6. ### Help with I don't even know what to call it

I have been going nuts trying to figure out what formula to use for this. I have a spread sheet with data entered something like this SHEET2 A B C <tbody> first last hours frank smith ed sham sara dart mel bent hank bruce </tbody> and another sheet...
7. ### Index Array with Double If Argument

Hi, We are trying to build a formula to analyze Amazon Ad Campaign Data. We have 2 sheets. The first is a raw data sheet where we paste the info in. The 2nd is a bid adjuster sheet which calculates based on the raw data info Our Auto Campaigns are working and look like this: Raw Sheet...
8. ### Nested RIGHT function not cutting off "A " or "An " but works for "The " ?

I'm trying to keep a list of titles in alpha order that disregards opening articles (A, An, The), but will still allow the full title to be displayed in another column. (E.g., "The Addams Family" displays in one, but gets shortened to "Addams Family" in the other.) The display part I've taken...
9. ### Combining "if", "Large" and "small" in the same formula

I can not seem to get the formula correct for the following scenario. If any number in column a, rows 1-3 is greater than zero, return the largest number. However, if the numbers are all less than zero, return the smallest number. I tried =IF(A1:A3>0,LARGE(A1:A3,1),SMALL(A1:A3,1)). I continue...
10. ### Copy a set of data based on a value in one column

I have been trying to set up a formula that will copy a line of data from one sheet to another based on one column. I've tried an If function, but this leaves alot of empty lines, and I've tried a VLookup but it creates alot of duplicates, and deletes the line below when new data is added from...
11. ### Find Text within a pivot table

I have a very long pivot table that changes monthly due to a refresh from an external data source. The table is grabbing total dollar amounts from several locations. I am trying to write a formula on a seperate tab that states, if there is "Seattle" in the pivot table (anywhere), then the...
12. ### VBA Variable equal to formula with if() Max() Sumproducts() of Ranges

I've got several formulas that work in cells of the active sheet and I reference them from VBA code to create ranges. I'm trying to get the formulas out of the sheet cells and into variables in VBA that I can use for a range references. The first step is to find the starting point for the first...
13. ### Inserting arrows into an If() Statment - not working - help

I would like to insert arrows into an IF() formula in excel for my performance dashboard. I am trying to insert the up arrow, down arrow and side to side arrow using alt+0241, alt+0242, and alt+0243. For some reason the arrows are not showing up. Just these symbols are showing up - ñ, ò, ó Not...
14. ### IF Formula help needed - football results spreadsheet

Hi there, I have just found this site and hopefully someone can help with them problem im faced with. I have one sheet within the workbook with the Table shown below where I track the football results from all English and Scottish leagues. <tbody> WEEK HRES HSCORE HOME AWAY ASCORE ARES...
15. ### Help with 3D Formula to extract text using INDIRECT() and IF()

Hi All - I'm working with a workbook that has 20 spreadsheets (tabs) and I want to search all 20 and extract a given text that is in 1 cell in each spreadsheet if the critera is met. I'm using the below formula to extract and sum (24hr) time in all 20 sheets, but I can't seem to modify it to...
16. ### Conditional Pull from Multiple Worksheets

I am trying to pull rows from multiple worksheets to a summary worksheet if the column "I" is equal to "Fail". Does anyone have a macro for conditional pull like this. Thank you very much.
17. ### Return Blank if FALSE

I am using a spreadsheet to feed a mail merge in word, and I've run into a problem: on sheet1 Column A through O contains data that I paste in from another workbook, Column P contains the following formula; =IF(NOT(ISBLANK(A2)),VLOOKUP(TRIM(A2),Sheet2!A:D,4,0),"") and is working fine. However...
18. ### conditional formatting for dates

Hi All, I need some help with conditional formatting for dates. Heres my criteria for conditional formatting: 1. if date is within 60 days - green color shading 2. if date > 60 days from today but < 120 days - yellow color shading 3. if date > 120 days from today - red color shading seems...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back