Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Hello all, I'm going to try to explain my problem in plain english and hopefully you great folks can help me translate into excel!... I have a sheet with data in groups with blank spaces in between... so for example: Row 1 data Row 2 data ..... Row 8 data blank blank Row 11 data Row 12 data etc... I want a formula that say if certain criteria is met, for excel to search in JUST THOSE ROWS CONNECTED for specific text: So for example: in cell K13 I have this formula: =IF(A13="","",IF(AND(B13="Stock",C13="BTO"),0,I13*(F13*100))) Where it says I13*...

Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Thank you. But I left out one part. I wanted to say if AF7=2 and J7 = June, July or August, then k22*l22. Thanks again. >-----Original Message----- >Hi >try > =IF(OR(J7="JUNE",J7="JULY",J7="AUGUST"),K22*L22,"not defined") > >-- >Regards >Frank Kabel >Frankfurt, Germany > > >Michelle wrote: I am trying to figure out a formula that states, IF a certain row equals June, July or August, then mutiply by a certain row. I started a test formula and I am getting #Value errors: The test formula is =IF(J7="JUNE&...

I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

I have been using Money for about a year with no problem, until recently I installed a ZA Firewall. Now when I go to import Bank Statements, Money Crashes, and requests to send an error report to Microsoft. Can this be solved? James ...

Hi all just after a quick fix (and probably a very simple one too, lol) I want a cell to display 0 if a different cell is less than a given value (in this case 95). One of the issues i face is that the cell i want to display as 0 (in the above instance) is needing a calculation in there. b4 = the cell which will determine the outcome, less than 95=0 in cell b10 b5 = value b9 = value b10 = b5+b9 currently (but should display 0 if b4 is less than 95) All help appreciated :) yY Did you look in the help index for IF? in b10 =if(b4<95,0,b5+b9) -- Don Guillett Microsoft MVP Excel SalesAid...

Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

I created a form that links to a report that uses the statement below as a record source. Example: Select value_name,value_yr,value3 From Test Where value name = forms!testform!nametxt AND value_yr=forms!testform!yrtxt UNION Select value_name,value_yr,value3 From Test Where value_yr=forms!testform!yrtxt How can i get the result of the first select statement without getting the result of the second one? Why? You are asking for both. Just omit the second. I am confused. -- KARL DEWEY Build a little - Test a little "tope12" wrote: > I created a form that links t...

I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

Hello all! I relized with acc2003 when i use the statement "SendKeys "{Esc}", True" the systmem locks up for a second or two and the NumLock key turns of and on again. Is there a way to avoid this? Thank in advance, abe Sure, don't use "SendKeys "{Esc}", True"! Sorry, but this is a well known bug with SendKeys, and not using it really is the only way to avoid the problem! Exactly what are you trying to accomplish? Perhaps someone here can offer an alternative! Linq -- There's ALWAYS more than one way to skin a cat! Answers/posts based...

How may I customize the customer statement? Good luck. Statements in MSM:SB are seriously broken At the core, Invoices are seriously flawed.. I'm not sure what MS thinks an invoice is and what information related to one is used for, but IT isn't what I NEED from a SMALL BUSINESS package.. I'm realizing that MSM:SB is a joke and statements in particular are a cruel joke. "xsintrick" wrote: > How may I customize the customer statement? > > ...

Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Trying to find a macro that will allow me to find a blank cell in a column, and if the corresponding cell is populated, then tag that cell with a certain value. So, if Column AG is empty, but Column AM is not, then put an * in column AG, then loop it to look in other columns as well. So if AG = blank, but AM is not blank, then place * in AG, if AN = blank, but AT is not blank, then place * in AN, if AU = blank, but BA is not blank, then place * in AU, if BB = blank, but BH is not blank, then place * in BB, etc.... For a total of 15 segments On 9 mrt, 16:14, Kennedy <Ken...

Problem: I have a current Macro that does a goal seek. However, if any of 4 cells is blank or 0 then the Macro crashes. I am trying to write code that will stop the Macro if ANY of the four cells has a 0 or a blank value. Otherwise, run the Macro. I think it will take a series of IF/THEN ELSE statements but I am not proficient at writing them. Any ideas? Current Macro: Application.ScreenUpdating = False Sheets("Cash Flow").Select Range("D49").Select Range("D49").GoalSeek Goal:=1, ChangingCell:=Sheets ("Program Input").Range( _...

Hi I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3 includes a number (variable cell). I would like to count how many numbers of the column D7:D200 are >F3, but in successive ranges of D column. (That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and finally D7:D200 > F3). To do that I have applied in E7 the formula: Countif(D7:$D$7 ; “>F3”) and expand down to E200. Unfortunately the above formula returns zero in all applied cells. If I change the F3 in the formula using a stable number (2,3,4 etc) the formula works, but...

I need help on my if statement. I am populating selected cells in a spreadsheet by use of a userform. The polulating works great. I would like to adjust my code so that if no value is selected from from the combobox then the target cell will retain its original value/formula. Any help would be great Here is what I have so far. If ComboBox_Sub.Value Is Nothing Then ????? Else Cells(r, 11) = ComboBox_Sub.Value End If Shane One way: If Combobox_Sub.Value <> vbNullString Then _ Cells(r, 11) = Combobox_Sub.Value In article &...

I am working with 13 subjects, each subject has three marks (3 adjacent cells, the third cell is the average of the previous 2) . Cells AX15, AZ15, BB15 will have a formula to show the name of the subject with the values of "F1" or "F2" or "F1, F2" depending on the marks in the cells. When AX15<>"" fill cell AZ15, if both cells (AX and AZ <>"" then fill in cell BB15) Example: H15, I15, J15 has 3 marks (for English) J15 =average(H15:I15) J14 has the 50% of the mark, I10 has the name of the subject If J15<J14 then if H...

Need a formula to count the cells in a range with a background color yellow (6). You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you...

Can I use an if,then statement in a report where the field value comes from a value list? I've put in the expresssion but I'm getting nothing and the error is "The Control Source property of the selected control is an expression that includes a reference to the control. Circular reference will cause the expression to fail". Change the name of the control so it is different to the field name. "jveitch" <jveitch@discussions.microsoft.com> wrote in message news:FFE5B2D9-EC81-494E-9BDB-C15A4121352E@microsoft.com... > Can I use an if,then statement in a...

We are using GP 7.5 and becoming familiar with SOP module. As we are testing our statements, is there a way that the only items that print are the open invoices. We don't want to see partial payments or returns. I was able to just show one balance but we would like to show the invoice detail with nothing else. I am not sure if this is a strange request but I need to let our AR dept know the options. If someone could give me some input, I would appreciate. HELP!! Thanks Barb By 'open' invoices, I assume you mean invoices that have not been completely paid. In order to ...

Hi- I have a combo box that is populated by a list in the same sheet. The combo box contains 4 different categories of data that when clicked will autofilter the sheet named "excel" based on which category was selected in the combo box. Here is the code have: Private Sub ComboBox1_Change() Sheets("excel").Select Selection.AutoFilter Field:=8, Criteria1:="=0" Selection.AutoFilter Field:=10, Criteria1:="=1" What I want to add to this code is an If Statement that will change the sort based on what is selected in the combo. So say the comb...

Hi All, The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know ...

Hi. I'm trying to create a formula that will count how many numbers in a list of ten that are greater than a changing number in a cell. The cell changes as it has a formula attached to it. I used as the criteria >b1, but for some reason this doesn't work. As this number changes it has to be greater than whatever the cell value is at that time. -- Thanks! Stephen try =sumproduct(--(range>B1),1) "Stephen" wrote: > Hi. I'm trying to create a formula that will count how many numbers in a list > of ten that are greater than a changing number in a cell. T...