# formula and if

1. ### V lookup Across Multiple Tabs and Return Multiple values if found on a tab

hi all, Need your guidance if possible on how to amend the below array formula to do a lookup all tabs listed below? =IFERROR(INDEX('4.RANGING CONVENIENCE'!\$B:\$B,SMALL(IF(B\$1='4.RANGING CONVENIENCE'!\$A:\$A,ROW('4.RANGING CONVENIENCE'!\$A:\$A)- MIN(ROW('4.RANGING CONVENIENCE'!\$A:\$A))+1,"")...
2. ### Produce a new list based on values in a range

Hello, I have a very long list of products and values and I need to produce another list that list products by a range of quantity. My list looks similar to this:- <colgroup><col><col></colgroup><tbody> Potatoes 199 Peas 152 Carrots 1901 Cabbage 576 Turnip 800 Cucumber 100...
3. ### SUM and COUNT Issues

I am having an issue with "Too many arguments". I have a set of data that I need to know the quantity of items that meet two different criteria sets and the sum of said items from a range. The second criteria set is a value range, example - ">=25000",Range,"<=100000". I cant seem to make it...
4. ### Combining Two Formulas in 1 cell

Is there way to combine this IF formula: =IF(OR(ISBLANK(I2),ISBLANK(A2)), "",) with this DAYS formula: =DAYS(I2,A2) into 1 cell. What i am trying to accomplish is calculate the number of days left for a certain date but if the date (cell I2) is not entered yet I want the count down cell to...
5. ### VBA Check if Cell is Empty & Add to Database Sheet

Hi Excel Gurus, I'm working on an macro project and almost done. I'm just having a problem on this: I have 2 excel sheet, one is working file and the other is database. What the macro needs to do is to check the cell with blank category (in working file) and add below column data in the...
6. ### Modifying complex formula: Adding a condition

A while back, I received some phenomenal help from Nishant94 regarding a complex formula I was trying to build to capture unique number of overlap days based upon some criteria. The original thread is here: Count Number of Unique Overlap Days - Meeting Criteria The formula (CSE) is this...
7. ### Insert a value in a cell without using formula in the same cell

Hi, I want to default value of a cell into multiple cells based on a condition For example : If cell C10 = "Yes", then put value of cell F10 in multiple cells like C42, I42, G42. If cell C10 = "No", then the user can enter their own values in C42, I42, G42 etc. I cannot put conditional...
8. ### Formula Assistance

Here is what I'm looking for: Column A Column B Column C 120,000 50,000 X 100,000 25,000 110,000 25.000 X 330,000 100,000 Grand Total I would like a formula that searches Column C for an "X" and then...
9. ### Data formula

Hello, I’m newbie and I need put in validation data a formula for check the next: • In column "H". If the column "G" is greater than 0, it must be greater than 0. Otherwise, it must be 0. Help me please Thank you n Regards.
10. ### Formula needed

Please can anyone help me to make a formula for this sheet? Thanks in advance!
11. ### Macro breaks Excel filter when searching products

Hi There! Im Juan from Argentina. I've been working around with a datasheet thats used to make an internal order within a small company. I've used a macro (not written by me) to have a "search" dialog box. You can searh all the items you need and place de units you need in column D but the...
12. ### Updating formula to sort and exclude zero values

For the formula below do you know of any way formula could be updated so that the sheet can automatic sort by the qty so the zeros or blanks rows would not be shown =IF(OR(G13="",Input!G13<=0),"",Input!H13)
13. ### If person has both a Roth and 401K display earnings amount once in gross earnings row.

If the person has a 401K & Roth, we only need to report the Gross Earnings once. I can't get the formula right. <colgroup><col><col><col span="2"><col span="5"></colgroup><tbody> Name Date Earnings Gross Earnings Type 401K 401L Roth Total Name 1 6/18/2012 574.75 401 28.74 28.74...
14. ### Need cell to show blank but number comes up due to adjacent formula

Hi, I have a formula in cell picking up running values. But when nothing is there, its giving me a number. I want it to show blank. I assume because there is a formula in that adjacent cell. this is what it shows. Where as E is the customer its picking up from column A, and F is the amount of...
15. ### If result from a formula contains a character, then...

Dear, I have the following issue, and looking for possible assistance. The formula I currently have is broken into three pieces: It looks at cell A4 if empty, otherwise to process the second part... It looks if a separate table column E:E contents, and compare with a concatenation of active...
16. ### How to set IF rule based on grouped data

Good Day, I wish to return either "R" or "NR" in a given column. This needs to be done on a file level (Column "FILE_REF"), so the function needs to be performed per file. If column "READY1" contains ONLY "R" values for a specific FILE_REF, then it must return "R" to ALL rows containing that...
17. ### Increment a date by a number of days, months or years

Hi, I have a date which I will need to increment the date by a day, month or year from the actual date. This is based of a list of fields below and is different per the input. Also each resultant date has to be a business day i.e not a weekend day or a bank holiday. I will forgo the Bank...
18. ### Index match if and

Hi, I have been puzzling over an Index match and formula for a few weeks now and was hoping someone could give me a nudge in the right direction. I created the below formula which works on one spreadsheet (a). When I try to extend the formula to look at 2 columns (different value in each) I...
19. ### Formulas in data validation range causing blanks

I have a workbook where users can input job titles in two different ranges (A2:A6 & A8:A12). Only one range would be filled at a time, but I want to use one data validation list for both ranges without blanks showing up within the list. The problem is if I use formulas to generate the range for...
20. ### Multiple IF values in one cell?

Very simple time sheet question for a formula that says “if the value inthis cell >8, then put the answer to value-8. If the value is <8, just put the value 0 in this cell “ So, my time sheet looks like this: <tbody> In out Hours OT OTusedForMakeUpTime CumulativeOT 7.00 20.75 13.75 5.75...

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### 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