Free Microsoft Excel 2013 Quick Reference

# How to add to sumifs formula to sum if value does not equal value

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?

## Related Results

### IF - how to add more than one IF

I have figured out how to return the correct "answer" based on an IF formula, based on one parameter only (either true or false). This is the formula in cell G2 of the first worksheet called MREC-1 data, and it is based on the age category in column F. What I would like help with is returning the correct answer from the other 3 possilbilities (what the correct answers should be are stated in cells H3, H4, and H5. Can someone please show me how to add the other 3 "IFs" to my formula? thank you

### How to add date format into if statement?

Does anyone know how to add date format into if statement? such as
If(A1="1-Mar-2005", 1,0)
Thank you
Eric

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

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

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

Hi,

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?

### Would conditional sum work for this? (does not equal zero)

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

TIA!

### IF function does not equal

I want to create a simple IF function for selecting cells based on what it does not equal. =if(a1 does not equal 1, c2,""). How would I go about doing the "does not equal" part in excel?

### How to write a formula in Excel

I have been trying to figure out how to write a formula in excel that does certain things. Here are the things I am trying to do.

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.

Cheers
Larry

### How to add this formula in vba?

Hi All,

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,

### How to delete the ENTIRE row if it does NOT include a string?

Hey Guys!
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

### IF (does not equal) Statement

Hi guys

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

```

### Macro to delete entire rows where O2:O does not equal a date

Hi
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
Complete```
Then 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/2011```
Any ideas?

Cheers
Rain

### How to add to "IF" formula?

What I’d like to do is depending on what is chosen from the drop down box I would like for it to bring it up on cell B7-D7.

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

### Can't add a Array formula to a VBA formula code

I have this fomula code below

```
VB:
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

```
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 +

=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?

### How to change range via values in other cells?

Edit: this has been solved - solution below in edit there

This is the code:
```
VB:
(\$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

```
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.
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

### SUMIF SUMPRODUCT SUM(IF

The scenario:
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

### How do I enter formula sum(range+range)*0.15 sumif(range>=3)

I'm not sure how to enter this formula:

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.

### How to add formula to existing macro

Hi all

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 Sub```
I 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

### How to correct this formula?

I have used following formula to sum where the column F is not equal to blank. But it display #VALUE! But after hitting Ctrl+Shft+Enter seems OK. How to correct this formula? Your suggestion is highly appreciated. Thank you.

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

Indra

### How to add formula in drop down bar?

i want to know that how to add formula in drop down bar? and can i do this that when we select one value in drop down bar so it can give the result or value on other Cell...?

### How to use #AND# in an if or sumif

Back in the old Lotus 123 days, I remember being able to use #AND# or #OR# in
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?

### Information on how to do two formulas and if its possible

Hi There,
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

### How to add a Single " i.e. (DOUBLE QUOTE) in a Concatenated Formula?

How to add a Single " i.e. (DOUBLE QUOTE) in a Concatenated Formula?

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:

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

Warm Regards
e4excel

### How to make a figure show a value in another cell dependent on a cell with text

How to make a figure show a value in another cell dependent on what line of text is chosen in an another cell?

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)

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

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.