Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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?


Post your answer or comment

comments powered by Disqus
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

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

--
dips112
------------------------------------------------------------------------
dips112's Profile: http://www.excelforum.com/member.php...o&userid=27825
View this thread: http://www.excelforum.com/showthread...hreadid=473321

How to link cells so that if one is not answered then the other cannot be filled in

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?

Thanks for your help.

=BZ5+BW5+BT5+BQ5+BN5+BK5+BH5+BE5+BB5+AY5+AV5+AS5+AP5+AM5+AJ5+AG5+AD5+AA5+X5+U5+R5+O5+L5+I5+F5+C5

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

TIA!

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?

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.

thanks for your help.

Cheers
Larry

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,
Adam

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

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

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

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

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?

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

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

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.

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

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

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

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?

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?

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

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)
(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.


No luck finding an answer? You could always try Google.