Free Microsoft Excel 2013 Quick Reference

I have a sumifs forumla which is providing a total based on 4 criterias. However I would like to add another criteria if the range of data doesn't equal 46. How can this be achieved?

- IF - how to add more than one IF
- How to add date format into if statement?
- How to link cells so that if one is not answered then the other cannot be filled in
- How to link cells so that if one is not answered then the other cannot be filled in
- If B1 does not equal "Apples" then do
- Would conditional sum work for this? (does not equal zero)
- IF function does not equal
- How to write a formula in Excel
- How to add this formula in vba?
- How to delete the ENTIRE row if it does NOT include a string?
- IF (does not equal) Statement
- Macro to delete entire rows where O2:O does not equal a date
- How to add to "IF" formula?
- Can't add a Array formula to a VBA formula code
- How to change range via values in other cells?
- SUMIF SUMPRODUCT SUM(IF
- How do I enter formula sum(range+range)*0.15 sumif(range>=3)
- How to add formula to existing macro
- How to correct this formula?
- How to add formula in drop down bar?
- How to use #AND# in an if or sumif
- Information on how to do two formulas and if its possible
- How to add a Single " i.e. (DOUBLE QUOTE) in a Concatenated Formula?
- How to make a figure show a value in another cell dependent on a cell with text

If(A1="1-Mar-2005", 1,0)

Thank you

Eric

be filled in

--

dips112

------------------------------------------------------------------------

dips112's Profile: http://www.excelforum.com/member.php...o&userid=27825

View this thread: http://www.excelforum.com/showthread...hreadid=473321

I have worksheet Food which has 2 columns.

In cell A1 I have a data list pulldown menu with the following values:

Lettuce

Spinach

Apple

Cucumber

In cell B1 I would like to have the following:

IF A1 does not equal "Apples" THEN write the value of 2 * 2.

Nothing needs to happen if A1 does equal "Apples".

Do I need to do this in VB Script? Or can it be expressed as a function?

Thanks for your help.

I only want the cell to be included in the sum if it does not equal zero.

TIA!

1. if I put a number (1,2, 3 ect) in we will say c1 I want D1 to say WW1 or WW2 ect depending on the conflict.

2. If I put a number (1, 2, 3 ect) in say F1 I want a proper name to show up in G1. Like 1 in F1 and in G1 I want a name like Chicago.

I hope that someone gets the drift of my questions. I have not written any formulas for numerous years and I am totally lost in Excel, thus I am solicitating someones assistance.

thanks for your help.

Cheers

Larry

Could you tell me how to enter this formula into a cell using vba please?

=VLOOKUP(B2,Title!$R$9:$S$20,2)&"-"&hide_RecCost!C2

Thanks,

Adam

On the attached file I have 2 columns.

I would like to have a macro that delete the ENTIRE rows that do NOT include the string "nikon" OR "d3100".

I mean that If any of the rows include "nikon" OR "d3100" (or both) it should stay, otherwise the entire should be deleted...

Can someone teach me how to do that?

Also, can someone show me a macro that do THE EXACT OPPOSITE?

I mean, if any of the rows include "nikon" it should be DELETED...

Many Thanks

I've got a function which I need a little help on please:

I'm currently using:

but need to use something along the lines of:

How do I code the part in square brakets, ie a value does not equal?

What I'm finding currently is that with a singular $G19>94.5% function, the cells with an NA text value are being copied across as a 100% value rather than #NA.

Thanks

Jan

Can someone suggest to me a good efficient macro that would delete rows where O2:O does not equal a date. So if I have this information in the column O as below:

HEADER Complete Complete Date in past 03/05/2011 Complete Complete No info Quarrantined Complete Complete Complete Complete Complete 18/04/2011 18/04/2011 Complete Complete CompleteThen after the macro run it would only leave me header row and the rows with dates like this.

HEADER 03/05/2011 18/04/2011 18/04/2011Any ideas?

Cheers

Rain

I’ve managed to bring over John but what if Joe was chosen? How would I add to the same formula where if Joe is chosen to bring his information instead?

In this case John = 2, Joe =3, Mary =4 and Jane =5…

Oh and any recommendations on what reading will help me understand on how to add more criteria to a formula??

VB:Look at the formula that is bold I want to add that formula to the formula code above. The formula should go right after the plus sign +A.Offset(0, 2).Formula = "=SumProduct(--(A1:a" & maxrow & "=" & Db & "With Pattern Change" & Db & "),C1:c" & maxrow & ")" & "+If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

=SUM(IF(FREQUENCY(IF(A2:A83&B2:B83"",MATCH(A2:A83&B2:B83,A2:A83&B2:B83,0)),ROW(INDIRECT("1:"&ROWS(A2:A83))))=1,C2:C83))

I just cant figuer it out how to add the formula maybe because the formula I want to add is a array.

Any Ideas?

This is the code:

VB:So as you can see there is a huge range from $A$1 to a very large number. What I want to do is make the range determined by values in cells B1 and B2.($A$1:$A$58531=L1;ROW($A$1:$A$58531));ROWS($A$1:A1));1);"")If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Of course when I just enter B1 and B2 instead of that range it just takes it as B1 and B2 instead of looking inside the cells, taking the values and using those instead.

I have no clue how to make the formula take the values from cells B1 and B2 instead of their location as the range to search in.

Any help would be greatly appreciated.

Thanks,

booost

EDIT: Ok the reason I was trying to change this is because I didn't always want it to go through 1 to 58k because it is very resource heavy using this formula.

So this one here I got from another forum is better:

VB:(C1="";"";IFERROR(SUM(MATCH(L$1;INDEX($A:$A;SUM(C1;1)):$A$58531;0);C1);""))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Just type something in C1 and put code in C2 and drag down if you expect more results

Table as follows

4 R F A

3 S G B

2 T H A

4 U I B

3 V F A

2 R G B

1 S H A

For any given sheet, I currently use formula {=SUM(IF(((B1:B7)="A")+((C1:C7)="A")+((D1:D7)="A"),A1:A7))}

In this given case, the value returned should be and is 10 (4+2+3+1). Hunting for "F", the value returned should be 7 (4+3).

Problem is how to make it versatile enough for multiple sheets that contain the same format. Using SUMPRODUCT does not seem to really get the job done or causes a formula long enough to warrant vB. Looking for an alternative.

Using 2003

I want to add two ranges and multiply the total by 0.15, but only if the

range of another cell is "less than or equal to" 3.

I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

Sub Fill_Blanks() Dim MyCell As Range Dim LastRowInColumnC As Integer LastRowInColumnC = Cells(65536, 3).End(xlUp).Row For Each MyCell In Range("D1:D" & LastRowInColumnC) If MyCell = vbNullString Then MyCell.Value = 0 End If Next MyCell For Each MyCell In Range("E1:E" & LastRowInColumnC) If MyCell = vbNullString Then MyCell.Value = 0 End If Next MyCell For Each MyCell In Range("F1:F" & LastRowInColumnC) If MyCell = vbNullString Then MyCell.Value = 0 End If Next MyCell End SubI would like to add a formula to the above code, which conforms to the following based upon Row 2 downwards:

e.g. for F2 it would be If(AND(D2>0,E2=""),9.5,0) Tried to utilise some similar examples for this type of code but I they did not work.

TIA ...spellbound

=SUM(IF((F2:F26<>""),E2:E26)

Indra

a condition statement. How is this done in Excel?

For example, if I wanted to use =SUMIF and sum only items where column A met

criteria 1 and colum B met criteria 2, how would I do that?

I was wondering with regards to using, Microsoft office 2007 excel, if you could give me advice on how to do two things, the first: I would like to know how when typing in a text in one cell say cell B2 it automatically causes it to pop a different text in cell C2 next to it. For example, if I typed in powerboat level two in B2, in box C2 it would pop up, for example, can run powerboat. Is this possible? My second questions was taking a number value and then in few boxes, being able to determine how to divide that number by three types of numbers, 12 being the maximum and 9 the least. I know I can do this individually however, i need it to work out without a decimal and split that one number into a division of the above, for example if i have 18 that can split into 10 once and 8 once. Is this possible to set up a formula automatically to do this itself?

Thanks

Dear Forum,

I am trying to make some Automatic Sentences for VBA Syntax..

For a very simple line such as

Range("A1").Value = 100

.

.

.

.

.

Range("Z1").Value = 100

I am trying to make this code in Excel Sheet and then simply Paste it in VBA Window, however I do not jnow how to get the DOUBLE QUOTES in a formula.

I am writing this formula in cell A2 as below:

=CONCATENATE("Range(",""",ADDRESS(1,ROW()-1,4),""",").Value = 100"

So How Do I get the """ Double quotes in the formula without using any additional cell references?

Warm Regards

e4excel

In Cell A (Sheet1) I have a drop down menu with "Stock" " Sales "Advertising"

The drop down menu is on sheet2,

(Stock= Sheet2!A1)

(sales=Sheet2!A2)

(Advertising=Sheet2!A3)

in cell B is blank but will have a numerical value added when needed.

I have three columns C, D, E

C is STock

D is Sales

E is Advertising

If A1 = "stock" and B1 has a value typed in then C1=B1

but if B1 is left blank then C1 should not have anything in it or equal zero

and using the formula underneath C1 contains #VALUE when B1 is left blank or "Advertising" or "Sales" is selected in A1

=IF(FIND("Stock",A1)>0,SUM(B1))

This formula does the calculation but #VALUE! appears if "Stock" is not selected,

how can I stop this or equal C1 to 0 if "Stock" is not selected.

I have been racking my brains for nearly 24 hours and searched many forums please someone put me out of misery and help or at least tell me if I am going about the whole conundrum the wrong way.

Thanks in advance.