# Counting Functions - If Col A =5 And Col B < Col C

Oct 3, 2008

I'm trying to find a function for the following statement:

=number of rows where columnA="value" AND columnB<ColumnC

So this formula would return 2 from the below table if the "value"=5...

X A B C
1 5 5 6
2 5 8 9
3 1 6 7
4 5 9 8
5 5 9 9

## 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 aren’t useful in Visual Basic. For example, the Concatenate function isn’t 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.

## Counting Matching Values In Two Separate Ranges Without Counting Duplicates?

Jan 1, 2014

I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.

I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)

B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.

Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13

H1 Total of matching numbers in cell range K1:P11

## Formula Counting Attendance - Counting 1 Day Too Many

Feb 3, 2014

I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls

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

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

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

## Sum Functions - Add All And Return A Value

Jun 9, 2014

So I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.

To visualize, i just want to add up the students that have not attended a class.

## Functions Use For Various Conditioning?

Jun 19, 2014

I wish to set 2 different cases (good and normal sales) for a list of data.

During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type

Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014

For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type

Sample file attached.

## NESTED IF And AND Functions?

Jun 30, 2014

I am trying to write a formula that will satisfy the following:

C1 and G1 are number values.

IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.

## Sumif Functions

Jan 8, 2009

I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.

## Marco Vs Functions

Nov 23, 2009

Currently, all of our data in Excel are linked by using Excel Functions between workbooks and some of the files are so large that it may take 10 minutes to open and copy and paste a cell may take three minutes (which is ridiculous slow).

Pardon my ignorance, my understand of Excel VBA/Marco is about automation. Even i use marco to run the excel, i still need formula in the cell in order to perform the task, right?

## Worksheet Functions In VBA

Jan 15, 2007

Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:

= left(worksheet name! row(1) column.range("startno"),2

## Check Box Functions

May 18, 2009

I have a cell (D19) that I want to be automated to return a value based on a formula (it will equal Cell D15). But I also want to have the ability to allow the user to override this and enter a manual value if needed. My idea on how to do this is having a check box with a macro where if it is checked it will unlock the cell and clear out the formula and when checked it will reenter the formula and lock the cell.

Also same form - I need a check box that will check all applicable check boxes (all boxes under Deductions to be taken). Can anyone assist me in setting up these check boxes so that my functions will work, or if there is a "better" way of providing these functions please let me know.

## Interest Functions

Dec 3, 2005

The file I am working with is available here

(1) I need to work out the value of 2 years worth of interest on a loan - details would be

Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...

the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)

The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...

(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...

what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee

## If/And Logic Functions

Jan 25, 2007

There are three situations that should yield the answer "overdue" and a fourth and fifth situation that should yield an answer "on target". I'm new to functions and have spent SEVERAL hours on this

{=IF((G4<>0,F4<\$g\$1="Overdue")+(F4<G4="Overdue")+(G4<>0,F4>\$g\$1="On Target")+(F4>=G4="On Target"))}

## Nested If/or Functions

May 7, 2009

IF/OR function below is not working

=IF((OR(B14=1,2,3,12,15,16)),"A",IF(OR(B14=4,5,6,7,8,9,10,11,13,14,21,40,41,42,43,44,45,46,47,50,51, 52,53,54,55,56,59,60),"B",IF(OR(B14=20,30,57,58),"C",IF(OR(B14=33,31,32,34,35,22),"E F OR G",IF(OR(B14=36),G,"ERROR")))))

## Combine 2 Functions Into 1

May 27, 2009

I tried to do a search for this but the search function was not working.

I have an excel sheet that I can scan Mac address into. The reason I created the sheet is because when I scan them in they are uppercase and do not have separators. I would like to combine 2 excel functions into 1

This one

=LOWER(C2)

and this one

=LEFT(E2,2)&":"&MID(E2,3,2)&":"&MID(E2,5,2)&":"&MID(E2,7,2)&":"&MID(E2,9,2)&":"&MID(E2,11,2)

## Nesting AND / IF And SUM Functions

Jul 11, 2014

I am trying to add up a specfic column in Excel based on the parameters or two different ones.

I.e If Col B is blank and Col D is 03 then add up the value in Col P

But I can't seem to work out how to do it, I have tried conditional sums and everything else I can think of, but it is still not working.

## Chart Functions

Mar 30, 2006

is there any way I can make the chart inputs variable. I have an array of numbers. lets say 100 of them, these are linked cells and depending on the input I might have less than 100 numbers in these 100 cells. All the cells which have numbers will appear in a block first and all the non number cells will appear below them as blanks in anotherblock. Now i have to create a chart from these numbers, so at times I have to take the 90 cells where there are numbers and other times I have to take 95 cells. Is there any way I can get over this.

## Vlookup & Right Functions In VBA

Aug 25, 2007

I'm trying to write a macro that looks up Column A so that each time it finds "Agent" as the first word in a cell, it takes that whole cell and copies it to another sheet.

I tried creating an array function that combines the If, Vlookup, and Right() and Left() functions, but no luck. Ideally, I would want this as a VBA macro.

## Custom Functions

Dec 3, 2008

I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?

## Multiple Functions

Jan 25, 2009

For the odds range, i have already two cells(the upper(F9) and the lower(E9)) and i have a cell named the current odd(G9). What are the formula together for the attribut cell(H9) for the following data:

1.0 : The current price is outside and below the odds range spread. For example, if the range is between 2,00 and 2,20, the range’s attribute will be 1.0 if the current price is below 2,00
NOTE: Obviously the formula for this one is =IF(G9

1.1 : The current price is on the bottom edge of the “odds
range” figure. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 1.1 if the current
price is 2,00
NOTE: The formula for this one would be =IF(G9=E9,1.1)

1.2 : The current price is inside the “odds range” spread
and is placed one or two clicks from the bottom edge. For
example if the range is between 2,00 and 2,20, the range’s
attribute will be 1.2 if the current price is 2,02 or 2,04
NOTE: I have a hard time creating a formula with this one.

2.0 : The current price is outside and above the “odds
range” spread. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 2.0 if the current
price is above 2,20
NOTE: The formula would be =IF(G9>F9,2.0)

## Vlookup FUNCTIONS

Sep 30, 2009

IN NEED HELP WITH vlookup FUNCTIONS:
1-st table
ABCDEF12221.985

2-nd table

>40
0.1
40

## IF AND Functions Nested

Aug 20, 2008

I recently came across a spreadsheet an ex-employee wrote with an interesting IF-AND statement in it. The only way I've ever written one is:

=IF(AND(something=1, somethingelse>2), do something)

His statement was written in the following manner:

=IF(something=1*AND(somethingelse>2), do something, if(thing3>1*AND(thing4>1), do something)

## Use Of Logical Functions

Jun 16, 2006

why the following code within a macro does not work

Cells(x,11).Value = If(Or(Cells(x,136)="", Cells(x,134)="","",Cells(x,136)-Cells(x,134))

## Disabling Functions

Jul 5, 2006

is there any way of disabling the following functions:

adjusting the size of the horizontal scroll bar;
being able to scroll to the right to search for tabs covered by the scroll bar

the reason being I want to have a few sheet tabs hidden but still be able to see the data on the sheet if a button taking you to the sheet is clicked

## Run Functions From Another Workbook

Aug 3, 2006

How can I execute a function that exists in workbook "A.xls" from VBA code in workbook "B.xls"?

Something like:
' -------- Code in workbook "B.xls"
Workbooks("A.xls")!My_Function(param 1, param 2)

## Compound IF Functions

Aug 22, 2006

See attached spread sheet as its a difficult one to describe

We are looking to apply labels to a cell depending upon criteria, the spread sheet contains the scenarios.

## Functions To Find Value

Mar 9, 2007

I have struggled with this and can't make it work. I am not sure if I should have used a lookup function.

I have this function in a worksheet:
=IF(AND(I62<F62,I63>F63),B64,"0")

The problem is that cell I62 may contain a value or 0. If 0 then I need to go back up through four cells I61 to I58 to find the value. Only one of the cells I62 to I58 will contain a value. The rest will be 0.

To try and do this I wrote this function but it does not work.
=IF(AND((IF(I62>0),I62,"IF(I61>0),I61,"IF(I60>0),I60,"IF(I59>0),I59,"IF(I58>0),I58,"I27""""")<F62,I63>F63),B64,"0")