Combining COUNTIF And AND Functions

Jul 6, 2006

The format for combining COUNTIF and AND in a
single function - I can't seem to get the format right.

What I want to do is COUNTIF range 'Issued_by' = A8 AND range
'Period_all' = 1

ADVERTISEMENT

Combining Two Functions Into One Cell?

Jul 23, 2014

I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")

Here is a sample table with showing the data and result

4
5L
4,5L

5
6R
5,6R

5
7L
5,7L

6
10R
6,10R

6
3L
6,3L

View 1 Replies View Related

Combining OFFSET / Indirect And IF Functions

Oct 25, 2013

I roughly know what my equation should look like, but it keeps failing. Its all part of a wider spreadsheet but I will only put down that bits that impact this part of the sheet.

In my cell I need it to input a value from a different spreadsheet. The input value is text.

I currently use this formula and it works perfectly for what I need.

=(OFFSET(INDIRECT("sheet2!D"&$A7),R$6,25,1,1))

This would a return a variety of results such as "excluded","compliant","non-compliant".

I would like to arrange an IF function so that if the answer is Excluded to keep it as Excluded. But anything else to use this formula instead.

=(OFFSET(INDIRECT("sheet2!D"&$A7),(R$6-2),25,1,1))

View 3 Replies View Related

Combining User-defined Functions

Oct 13, 2006

Say I have written three user defined functions: first(a,b,c); second(a,b,c);third (a,b,c), which all give value for a,b,c inputs. How do I write a VBA program that uses the functions above? Say the code is called Combine(), after the user have typed in the value of a,b,c, the function Combine=first+second+third.

View 4 Replies View Related

Combining COUNTIF, RIGHT, <

Jan 10, 2008

Excel 2007

I am trying to count how many cells have the last 2 digits of 84 or less. I tried this formula, but it is not working.

=COUNTIF(RIGHT(H4:H129,2),"

View 9 Replies View Related

Combining 'counta' And 'countif'

Oct 1, 2008

I'm trying to create a report to show daily statistics for internet traffic. I have a large table showing a daily breakdown of performance, a table showing combined cost for all "Mondays", "Tuesdays" etc in the month and I also want a table to show the "average" performance for a typical "Monday", "Tuesday" etc.

The report will be used on a daily basis and I realised that until there is a full compliment of data the daily averages table would be inaccurate as it will be dividing the overall total for each day by the total number of occurances each day has in a typical month. This is wrong because if we are only halfway through the month then we will be dividing by days that haven't arrived yet.

View 3 Replies View Related

Multiple Criteria For Countif Functions

Nov 11, 2006

I need to create a formula that counts the number of times that an age range appears within a column. In column G, there is a list of ages based on a demographic collection. The ages range from 13 to 50+. I want to designate another cell to count the number of times the characters between 13 and 18 occur within that column. I have =COUNTIF(G8:G20,"13") How do I add "14", "15", "16", "17", and "18"?

View 2 Replies View Related

#DIV/0! Dividing Multiple Countif Functions Into Value

Jul 19, 2007

I have this formula

= COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")

that returns the number 2 (which is correct). However, if I precede it with

AZ6/COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")

it returns a DIV/0 error, even though AZ6 has a value of 24.

Surely 24/2 would return a value of 12? NB AZ6 cell value is derived from the result of a formula.

View 3 Replies View Related

Functions Compared With VBA Functions

Mar 14, 2008

I am aware of the following topic in the VBA Help file:

"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions arent useful in Visual Basic. For example, the Concatenate function isnt needed because in Visual Basic you can use the & operator to join multiple text values."

And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)

However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.

View 9 Replies View Related

Combining IF, AND, OR.....

Jan 7, 2009

I have some code where I use and statements, as follows:

View 4 Replies View Related

Combining IF And IS

Jan 14, 2007

I want to see if a goal has been met and get a "Yes" or "No" so I wrote
=If(A1>=A2),"Yes","No"

But on months not worked where goal and production both equal 0 then I am getting a return of Yes. How can I say is A1 >= to A2, but if both are 0 return N/A?

View 12 Replies View Related

Combining AND And OR

Apr 20, 2009

I am having problems combining AND and OR into a single statement. To start I have a statement =IF(AND(B978="P",R978=0,S978=0),"processed",""). If B=P and R=blank and S=blank then display PROCEED else make blank.

What I want to do is check if S is blank or "N" but when I try to add an OR I get error messages regarding missings )'s =IF(AND(B978="P",R978=0,OR(S978=0,S978="N"),"processed","").

View 3 Replies View Related

Combining Two VBA Together

Oct 11, 2012

I have two vba the first one records the date & time of a cell change in column a and places this in column b

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Target.Offset(0, 1) = Date & " " & Time
End If
End Sub

The second one is a function to get the User logged in to windows.

Function UNameWindows() As String
UNameWindows = Environ("USERNAME")
End Function

What I really could do with is a way of incorporating the user name in the first function so it records date, time and user?

View 3 Replies View Related

Combining First & Last Name

Sep 20, 2006

I have two seperate columns, First Name (Column B) and Last Name (Column C). Is there a way a macro can combine the two into one cell Last Name, First Name.

View 3 Replies View Related

UDF - Combining VLookups

Dec 17, 2012

I am trying to write a UDF function where I lookup if a value is on a range on different sheet. I would like to be able to write what I want the result to be in the function. I also hard coded that the function need 3 range and 3 results, but I know it is possible the make the number of component vary depending on what the user writes. I would like my function to do that.

Here is what I have written so far :

VB:
Function find_para2(var As String, rng As Range, result1 As String, rng2 As Range, result2 As String, rng3 As Range, result3 As String)
If WorksheetFunction.IsNA(WorksheetFunction.VLookup(var, rng.Address, 1, False)) = False Then
find_para = find_para & result1 & ", "

[Code] .....

View 6 Replies View Related

Combining If And Lookup

Jan 31, 2009

I need to find a formula to show the time allowed for each person, now they recomend combining 'if' with 'lookup'. now I may be having a blonde moment but I have tried to figure it out for a while now and i am stuck.

View 14 Replies View Related

Combining Two Columns Into One

Feb 27, 2009

Trying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!

View 2 Replies View Related

Combining Several Rows In One

Jan 16, 2014

I have a big amount of data and want to consolidate it.

I have identical text in Columns 1,3 and 4, but the text in Column 2 is different for all.

How do I make 1 row instead of 3 with Column 2 consolidated?

See the attachment : Excel_list.xlsx‎

View 2 Replies View Related

Combining Records Together

Oct 25, 2008

I have a problem in excel that is very urgent to resolve. I have a table that contains duplicate records e.g two people living at same address. I want to merge those records whose address field value is same. Find attached an excel sheet that contains the exact data and the exact output that I want. I know it requires VBA coding.

View 4 Replies View Related

Combining Code..?

Jun 11, 2009

I have two different bits of code as follows:

View 3 Replies View Related

Combining =REPT And =INT

Oct 29, 2008

ok - I have numbers that need to be converted to 12-digit numbers with leading zeros if they are less than twelve digits. for example, 1234567 would turn into 000001234567 to have 12 digits. to do this, i use:

=rept(0,12-LEN(A1))&A1

additionally, i need to strip off the last three digits and replace them with three zeros. my example would now become 000001234000. assuming the result of my first formula (above) is in cell B1, i would use:

=INT(B1/1000)&"000"

Is there a way to combine these two functions into one formula to make this conversion process more painless? Or is there another formula/function I can use that I haven't thought of or do not know?

View 9 Replies View Related

Combining IF Statements With Max Value?

Nov 26, 2011

I'm having a mental block here. I need the below statement to have a max value of (B14*B4) in the instance where

(B17-B19)*0.7 exceeds (B14*B4),
IF(B17-B19

View 2 Replies View Related

Combining 2 Macros Into 1

Mar 25, 2013

I got 2 macros that I want to run in one go. So to explain:

Here is the data of Sheet1 before the macro is run:

A
B
C
D
E

[Code]....

Keep in mind, when Macro2 is run it puts the data on Column W then X then Y etc as soon as the Column reaches 20 it moves on to the next one.

So my question is:

Is there anyway to combine both macros to just run it once instead of running it individually?

View 4 Replies View Related

Combining 2 Codes Into 1

Jun 9, 2013

Just starting to learn VBA. I have the 2 following codes.Can they be combined into 1 code.I have 2 buttons one say hide rows and the other button says unhide rows....can 1 button be used and when the rows are visible the button says hide rows and when the rows are hidden the button says unhide rows

Sub hiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = True
End Sub
Sub unhiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = False
End Sub

View 6 Replies View Related

Combining Two Subs Into One

May 20, 2014

I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.

Here's what I have:

Code:
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name

[code].....

But it's erroring out on me.

View 4 Replies View Related

Combining 2 VBA Formulas Into 1?

Jul 22, 2014

how to combine these two codes into one?

Code:
Sub DelTemp()
Dim LR As Long, i As Long
Dim pt As PivotTable

[Code].....

View 1 Replies View Related

Combining Two Macros To Run As One

Jan 12, 2007

I have two macros that I need to run as one and I am not sure how to call the second macro from within the first. I'm sure it's easy...If you know how... Thank you in advance.
--------------------------------------
macro 1
Sub PrintUsedRange()
'
Dim ws As Worksheet
'
For Each ws In Worksheets
Application.CutCopyMode = False
ws.PageSetup.PrintArea = ws.UsedRange.Address
With ws.PageSetup
.Zoom = False
.CenterHorizontally = True
.Orientation = xlPortrait
.FitToPagesWide = False
.FitToPagesTall = 1
End With
Next ws
ActiveWorkbook.PrintOut............

View 9 Replies View Related

VBA Combining Worksheets ...

Aug 21, 2007

Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.

What VBA code would merge the information from the four worksheets to Worksheet E?

View 9 Replies View Related

Combining Multpile IFs

Jul 15, 2008

Individually these formulas work - is there anyway I can combine them into one formula in one cell so the will look at the month and then perform SUM function?

=IF(I$2="apr",SUM(SUMIF('Raw Data'!$A$1:$A$64773,$A$1,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$74,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$75,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$76,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$77,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$78,'Raw Data'!D1:D64773)),"X")

Etc for all 12 months?

View 9 Replies View Related

Combining Columns

Aug 28, 2008

I have monthly reports that come to us from a Data Warehouse. They vary in size(row length). The problem is that the information in one column actually belongs in the next column (i.e. C to D). As this information in Column C is just an "offset" from Column D there is no issue with overwriting any data in column D. Manually, I could copy the columm and Paste Special, Skip Blanks, to column D.

I've been trying to do this in code as some reports can be fairly lengthy: As there are some blank rows, I've inserted this FinalRow procedure, to provide the size of each report:

FinalRow = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Rows(1).Row - 1

However, in trying to use the FinalRow value in a "Copy the contents from Column C....Cells(20,3 to FinalRow, 3) and then PasteSpecial to Column D (Operation:=xlNone, SkipBlanks:=True) has not been productive. It keeps producing a number of Errors, in every way that I have tried to make this work.

View 9 Replies View Related

Combining 3 Macros

Sep 26, 2008

I would like to combine the following 3 macros into one macro. Try as I might, I can not figure out how to do it?

Sub DeleteEmptyRows()

Dim Counter
Dim i As Integer

Counter = 5000
ActiveCell.Select
For i = 1 To Counter
If ActiveCell = "" Then
Selection.EntireRow.Delete
Counter = Counter - 1
Else
ActiveCell.Offset(1, 0).Select
End If...................................

View 9 Replies View Related

Combining 3 Or If Then Statements

Sep 29, 2008

I am trying to combine all 3 of these statements in a formula and the syntax seems to keep tripping me up:

=IF(AND(G27=" ", M23=0), " ")

=IF(AND(G28=" ", M23>0), F28+$M$23, " ")

=IF(G29=" ", " ", IF(G29>0, M23>0, G29+M23))

Basically, If M23 = 0, I need the cell to remain blank. But if M23>0 then the 2nd and 3rd formulas need to apply.

View 9 Replies View Related

Combining Two Or More Cells

Sep 28, 2009

I know a simple formula would 'almost' do what i want...but i was hoping for a simple feature....

i have several text cells that i want to combine into one cell.....

so a formula like =A1&B1&C1.... would work fine....but is there anyway to present this data a little fancier? for example separating each cell entry after a "break" or something....

View 9 Replies View Related

Combining The Rows

Jan 19, 2007

I've attached a spreadsheet with a simplified example of my situation. Notes appear at the far right to explain my desired result. I've read through lots of posts on the forum but haven't yet found anything to mimic.

I'm building a calendar to display promotion activity by week for multiple customers and multiple products. The source data only lists one event per row, so the current result is not as concise I know it could be. I end up with twleve different rows for customers with activity each month on the same product, where I'd like to see just one row with all activity for the year.

Can anyone assist with either VBA or a formulaic solution? I've tried a few things with if/and statements followed by a concatenation of previous rows, but this seems like it will not have the intelligence to calculate only rows that match, I keep coming up short. Can OFFEST be used in some way?

View 9 Replies View Related

Combining Two Formulas Into One

Feb 9, 2007

I have the following formula... in Cell T4 =IF($Q4="",(M4&(IF(L4="","",(" ("&L4&")")))),(IF( ISNA(VLOOKUP($Q4, Lookup!$A$1:$B$5,2,FALSE)),Q4,VLOOKUP($Q4,Lookup!$A$1:$B$5,2,FALSE)))) But I also want to replace the 'R(space)' in the cell at the same time. I currently do that in a separate cell using: =SUBSTITUTE(T4,"R ",""). How do I combine the two..so that it is done in one action ??

View 2 Replies View Related

IF THEN Functions..

Dec 4, 2009

I want to calculate the following...

If Cell A>40, Then the result should be ((Cell A-40)*23.25)
If it is not >40, Return 0.00

This is for a timesheet / overtime.

View 2 Replies View Related

Possible Functions In VB

Jan 12, 2010

Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:

View 4 Replies View Related

Use Of Vb Instead Of All My Functions

Apr 19, 2007

i have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.

View 7 Replies View Related

Combining Match And SUMIF

Feb 19, 2014

I have three columns (Location, Task, Time) that I want to gather information from and display on a separate page.

I've used SUMif Function to group task and time together. I want to separate this information between different Job Locations. I believe INDEX and MATCH functions need to be used??

For example: job1 will only display relative hours for that location, While Job2 location will display relative hours on a separate summary page.

View 5 Replies View Related

Combining Text And Numbers?

May 7, 2014

I have the correct formula but the formula is not converting over.

54Morningside Dr=F287&" "&G287

I am trying to combine 54 and Morning side Dr to one cell.

View 13 Replies View Related

Combining Multiple IF / AND Formulas?

May 26, 2014

Is it possible to combine the following three formulas into one formula?

=IF(AND(H5>59,(I5<5),(J5<15)),("Close Out"),(""))
=IF(AND(H5>59,(I5<15),(J5<5)),("Close Out"),(""))
=IF(AND(H5>55,(I5<10),(J5<3)),("Close Out"),(""))

View 2 Replies View Related

ADVERTISEMENT