I want to expand this calculation to include
IF(J5>100,VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),100,FALSE)
=IF(B5=0,0,IF(N5=0,0,IF(B5=1,IF(E5="INSERT",VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),J5+2,FALSE),IF(B5=1,IF(E5="ADVERT",VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),J5+2,FALSE),0),0)))))
My concern is in row 4 and 5 of the attached spreadsheet. The problem is that not all postings for the allocations will take place on or before the date the allocation starts. Therefore, i need a logical formula that builds on what already exists and determines if the "entered" date is > the "start date". and then catches up how far it was behind. Example:
contract 12,000
Contract starts January 1st and ends December 31 (12 Months)
Entered Feb 1
In the January column there chould be zero in feb there should be 2,000 and the rest of the months should be 1,000
I am looking for a way to say
IF A2>9 then B2 returns bronze
IF A2>24 then B2 returns Silver
IF A2> 49 then B2 returns Gold
IF A2>99 then B2 returns Platinum
I'm trying to create a SUMIF statement that that has an embedded OR statement within, and am unable to make it work.
Basically I want to say if "column Q" equals one of 2 criteria ("cat" or "dog") then sum the corresponding number in "column P" I've tried the following statement, but it is just resulting in a zero:
=SUMIF(Q9:Q32792,OR("cat","dog"),P9:P32792)
I've seen support on statements with multiple criteria in DIFFERENT columns, but not if searching for multiple criteria in the SAME column.
I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
I want to add another vlookup criteria into the statement below and im having some difficulty, its currently has two called "Name" and "QrtName", i want to add another called "PlaceName". I have a named range called "Data" for the data the vlookup options use. It is working fine other only i want to add the third vlookup option:
View 9 Replies View RelatedHow should my multiple If And Or statement syntax read?
On first button click If "BUY" or "SELL" is in column 83 then copy as the code indicates to sheet "Orders" as the code indicates. This it does.
On second button click don't copy the same "BUY" or "SELL" that has already been copied to sheet "Orders".
So the Countif should set J to greater than 1 for the previous copies and be ignored and only copy from sheet "Main" the new "BUY" and "SELL" that show up.
I'm getting double and triple copies of items already copied that should set J to > 1.
Code:
Option Explicit
Sub BuyCells()
Dim c As Range
[Code].....
how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.
Here is my code - it's ugly but it was working when I just needed it to move down one cell:
lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then
[Code].....
I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.
I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.
G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
how to add/remove criteria to a COUNTIFS statement, based on the value of a VBA UserForm Checkbox.
All of the criteria for the checkboxes are based out of the same column. Here is the similar code I currently utilize for my checkbox filter.
[Code] ..........
The code hides fields within a collumn based on the tag within the checkbox's properties. Now I am trying to create an addition that will add criteria to a CountIf formula in real time.
I need a formula that will tell me if EITHER two cells = a text word. I've tried a few things and can't seem to get it to work!!!!
See in the example of my spreadsheet below: If A2 = FALSE or B2 = FALSE then D2 should display "Allowed" if either are TRUE D2 should display "Not Allowed"
Is this possible!?!?! I've tried way to many different formulas and am close to giving up..
A
B
C
D
1
Not Slow
50%
FY
Change
[Code] ........
I'm trying to put together a SumIfs formula to find the total count of procedure codes during a certain time period, for a given market.
I have the Market column, Procedure Code column, Date column, and count of Procedure Codes column.
The range of Procedure Codes I want to total on are in cells C4:F4 and they are:
99307993089930999310
It's fairly straightforward with the dates in the SumIfs as well as markets, but I'm not able to get the range of Procedure codes to work like I want. These 4 codes are just the criteria that will use to pull the totals from, which comes from a much larger dataset.
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
How to leave a cell truly empty if the criteria of my IF statements is untrue. Currently, I'll write something like:
[Code] ....
But for some reason, when I copy and paste the resulting range of values elsewhere (to rid myself of the formula that determined them), the cells that did not return a value (where the statement is FALSE), are not recognized by a "Go To Special > Blanks" request, until I select all of the "empty cells" and clear them manually. Yet when I try to do a search on the same range for an empty space, I get no hits.
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies View RelatedMacro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies View RelatedI have a spreadsheet that I need some help with coming up with a multiple if statement(or maybe sumifs?) I don't know. Here is what I am wanting to do(In somewhat plain old english)
if c3:c43 = A9 add g4:43 than does the total = h3:43 if so display true else false
I have attached a copy of the spreadsheet.
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
I have the following columns:
Status (column C), New Status (Column D) and Date Sent (column M)
Status column (C) contains multiple stages of progress status (Being Reviewed, Filed Not, etc)
New Status (D) column is blank (this is where I will enter the formula)
Date Sent (M) has mostly blank cells but some with dates in them
I would like a formula in column D (New Status) which evaluates C (Status).
If C = "Filed Not" AND M is blank, then output "Filed Not - Not Sent"
If C = "Filed Not" AND M has a date in it, then output "Filed Not - Sent"
If C <>"Filed Not" then output whatever data exists in C
I can get each of these to work individually, however, I can't, for the life of me, get them to work into one equation.
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies View RelatedI have a formula that looks at 5 columns. If any one of these 5 columns meets the specified criteria, it will "Fail" the row. What I want is, when the row is failed, to display the column that was failed. Well I have that part under control using the following formula.
View 2 Replies View RelatedI want to say, if B1 = A then the value is 10, if B1 = B then the value is 20, if C then the value is 30 and D the value is 40.
If there are two conditions then I know it would be =if(b1=A,10,0) but this is obviously a bit more complex.
I would like to be able to search a group of cells for particular text,
placing the true of false value in another cell, using this formula;
=IF(ISNUMBER(SEARCH("ACP",A2)),"ACP","XXX")
My reason for this is that I have a large amount of data that I need to
break down. I have a column that is listed similar to below;
OR/ACP
OM/ACT
OR/MTS
O/O
The part before the / is 'system code'
The part after the / is 'module code'
I want to be able to have one additional column for system code and one
additional column for module code, without having to manually go through all
the data and make any amendments to it.
So as a result, (without the correct syntax) with 'S' being the system
Column and 'M' being the module column, what I would like to achieve is;
If cell contains "O/" then S1 = Office but if cell contains "OR/" then S1 =
Office Range but if cell contains "OM/" then S1 = Office Medium
I hope that makes sense.
Obviously repeated for the module column;
If cell contains "/ACP" then M1 = ACP but if cell contains "/ACT" then M1 =
ACT but if cell contains "/O" then M1 = O
I have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down
FA5 = option 1
FA6 = option 2
FA7 = option 3
FA8 = option 4
FA9 = option 5
FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
I was wondering if there is a more beautiful way of this formula
Code:
{=MAX(IF(IF(B14=A1:A12, B1:B12)>IF(B14=A1:A12, C1:C12),IF(B14=A1:A12, B1:B12),IF(B14=A1:A12, C1:C12)))}
A
B
C
1
A
10
10
[Code] ...
Highest B is 80
Dim b As Long
Dim last_rowB As Long
last_rowB = Range("K65536").End(xlUp).Row
For b = last_rowB To 2 Step -1
If Sheets("Main").Cells(b, 11).Value = Sheets("Main").Cells(b + 1, 11).Value & Sheets("Main").Cells(b, 11).Value <> "" Then
Sheets("Main").Cells(b + 1, 11).EntireRow.Delete
End If
Next b
If 2 cells are the same i want to delete the row of the 2nd cell, this works. But i dont want 2 delete cells that are blank, this doesnt work. For some reason it deletes everything in the table.
