Hi chaps, Excel 2003 I'm using the following formula to sum the number of times that the #3 appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value in Column AJ3:AJ1002 is between 2 other distinct values (located in a seperate table, cells I1320 & J1320). =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))) All works fine, but now instead of the #3 I need to introduce an OR function so that if either of (or both) the values in G3:G1002 & H3:H1002 exceed 3 (are 4 or greater), then it sums the number or times this occurs. Need to stress that if either or both of the two values exceed 3, then it sums the number of occurances. Many thanks for looking, hope you can help. Steve.

0 |

4/21/2010 9:27:01 PM

"Struggling in Sheffield" wrote: > but now instead of the #3 I need to introduce an > OR function so that if either of (or both) the values > in G3:G1002 & H3:H1002 exceed 3 (are 4 or > greater), then it sums the number or times this occurs. The following __counts__ the number of such occurrances in conjunction with the additional criteria, which is what I think you mean by "introduce an OR function". =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) *($AJ$3:$AJ$1002<J1320) *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0, "", SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) *($AJ$3:$AJ$1002<J1320) *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) By the way, here is a simpler alternative to consider: =SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) *($AJ$3:$AJ$1002<J1320) *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) with the Custom format: General;-General;"" ----- original message ----- "Struggling in Sheffield" wrote: > Hi chaps, > Excel 2003 > I'm using the following formula to sum the number of times that the #3 > appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value > in Column AJ3:AJ1002 is between 2 other distinct values (located in a > seperate table, cells I1320 & J1320). > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))) > > All works fine, but now instead of the #3 I need to introduce an OR function > so that if either of (or both) the values in G3:G1002 & H3:H1002 exceed 3 > (are 4 or greater), then it sums the number or times this occurs. > > Need to stress that if either or both of the two values exceed 3, then it > sums the number of occurances. > > Many thanks for looking, hope you can help. > Steve.

0 |

4/21/2010 10:36:01 PM

"Joe User" wrote: > "Struggling in Sheffield" wrote: > > but now instead of the #3 I need to introduce an > > OR function so that if either of (or both) the values > > in G3:G1002 & H3:H1002 exceed 3 (are 4 or > > greater), then it sums the number or times this occurs. > > The following __counts__ the number of such occurrances in conjunction with > the additional criteria, which is what I think you mean by "introduce an OR > function". > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > *($AJ$3:$AJ$1002<J1320) > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0, > "", > SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > *($AJ$3:$AJ$1002<J1320) > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) > > By the way, here is a simpler alternative to consider: > > =SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > *($AJ$3:$AJ$1002<J1320) > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) > > with the Custom format: General;-General;"" > > > ----- original message ----- > > "Struggling in Sheffield" wrote: > > Hi chaps, > > Excel 2003 > > I'm using the following formula to sum the number of times that the #3 > > appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value > > in Column AJ3:AJ1002 is between 2 other distinct values (located in a > > seperate table, cells I1320 & J1320). > > > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))) > > > > All works fine, but now instead of the #3 I need to introduce an OR function > > so that if either of (or both) the values in G3:G1002 & H3:H1002 exceed 3 > > (are 4 or greater), then it sums the number or times this occurs. > > > > Need to stress that if either or both of the two values exceed 3, then it > > sums the number of occurances. > > > > Many thanks for looking, hope you can help. > > Steve.

0 |

4/21/2010 10:37:01 PM

I wrote: > By the way, here is a simpler alternative to consider: [....] > with the Custom format: General;-General;"" I meant to add.... With this approach, the value in the cell will actually be zero, not the null string (""). So you would test for A1=0 instead of A1="" wherever applicable. That should not be a problem since your current formula will not return zero. In fact, it should be a plus since you can use A1*3 directly (e.g.) instead of some work-around like N(A1)*3. PS: Sorry about the reply with no additional comment. Clicked on the wrong button inadvertently. ----- original message ----- "Joe User" wrote: > "Struggling in Sheffield" wrote: > > but now instead of the #3 I need to introduce an > > OR function so that if either of (or both) the values > > in G3:G1002 & H3:H1002 exceed 3 (are 4 or > > greater), then it sums the number or times this occurs. > > The following __counts__ the number of such occurrances in conjunction with > the additional criteria, which is what I think you mean by "introduce an OR > function". > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > *($AJ$3:$AJ$1002<J1320) > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0, > "", > SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > *($AJ$3:$AJ$1002<J1320) > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) > > By the way, here is a simpler alternative to consider: > > =SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > *($AJ$3:$AJ$1002<J1320) > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) > > with the Custom format: General;-General;"" > > > ----- original message ----- > > "Struggling in Sheffield" wrote: > > Hi chaps, > > Excel 2003 > > I'm using the following formula to sum the number of times that the #3 > > appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value > > in Column AJ3:AJ1002 is between 2 other distinct values (located in a > > seperate table, cells I1320 & J1320). > > > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))) > > > > All works fine, but now instead of the #3 I need to introduce an OR function > > so that if either of (or both) the values in G3:G1002 & H3:H1002 exceed 3 > > (are 4 or greater), then it sums the number or times this occurs. > > > > Need to stress that if either or both of the two values exceed 3, then it > > sums the number of occurances. > > > > Many thanks for looking, hope you can help. > > Steve.

0 |

4/21/2010 10:41:01 PM

Thanks for that Joe, was struggling to bottom that one out. Have used the less simple one to for now as I understand it and it works! Forward march again 'til I hit the next brick wall. Cheers. "Joe User" wrote: > I wrote: > > By the way, here is a simpler alternative to consider: > [....] > > with the Custom format: General;-General;"" > > I meant to add.... With this approach, the value in the cell will actually > be zero, not the null string (""). So you would test for A1=0 instead of > A1="" wherever applicable. That should not be a problem since your current > formula will not return zero. In fact, it should be a plus since you can use > A1*3 directly (e.g.) instead of some work-around like N(A1)*3. > > PS: Sorry about the reply with no additional comment. Clicked on the wrong > button inadvertently. > > > ----- original message ----- > > "Joe User" wrote: > > "Struggling in Sheffield" wrote: > > > but now instead of the #3 I need to introduce an > > > OR function so that if either of (or both) the values > > > in G3:G1002 & H3:H1002 exceed 3 (are 4 or > > > greater), then it sums the number or times this occurs. > > > > The following __counts__ the number of such occurrances in conjunction with > > the additional criteria, which is what I think you mean by "introduce an OR > > function". > > > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > > *($AJ$3:$AJ$1002<J1320) > > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0, > > "", > > SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > > *($AJ$3:$AJ$1002<J1320) > > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) > > > > By the way, here is a simpler alternative to consider: > > > > =SUMPRODUCT(($AJ$3:$AJ$1002>=I1320) > > *($AJ$3:$AJ$1002<J1320) > > *(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))) > > > > with the Custom format: General;-General;"" > > > > > > ----- original message ----- > > > > "Struggling in Sheffield" wrote: > > > Hi chaps, > > > Excel 2003 > > > I'm using the following formula to sum the number of times that the #3 > > > appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value > > > in Column AJ3:AJ1002 is between 2 other distinct values (located in a > > > seperate table, cells I1320 & J1320). > > > > > > =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))) > > > > > > All works fine, but now instead of the #3 I need to introduce an OR function > > > so that if either of (or both) the values in G3:G1002 & H3:H1002 exceed 3 > > > (are 4 or greater), then it sums the number or times this occurs. > > > > > > Need to stress that if either or both of the two values exceed 3, then it > > > sums the number of occurances. > > > > > > Many thanks for looking, hope you can help. > > > Steve.

0 |

4/22/2010 9:23:01 AM

I need to automatically assign a sequential number based on the last four positions of a text field on a form based on the maximum value of part of the field +1 and based on the matching the value of the same field to that of a record in another table. In Table Service-Contracts I have a text field titled ARL TRACKING NO. Sample values are as follows: ARL-2009-0001 ARL-2009-0002 ARL-2009-0003 ARL-2010-0001 ARL-2010-0002 ARL-2010-0003 ARL-2010-0004 In Table FISCAL-YEAR I have a field called FY and there is one record which equals “2009”. In the sample above, I expec...

Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

I'm looking for a function that will reference a cell then lookup that cell in a table and use the multiplier in the table. I'm not explaining very well so Im hoping that my example will help you understand: Column A b c Sector P&L w/o Admin P&L w/ Admin PC 517.9 PC 0.0 PC 0.0 TD 1,993.6 TD (2,508.0) TS 431.3 TS 355.7 Table reference: FD 1.20 PC 1.03 PR 1.52 SIJ 1.00 TD 1.32 TS 1.63 In column C I would like to put a formula that; if column A mactches column A in table reference th...

I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

We have a very serious issue where we wish to convert (change) the functional currency of a GP system that has been in production for a very long time => has a lot of data accumulated. As far as I know this can not be done by any GP functions/utilities. Does anyone know of any ways that we could go about doing this or any third party products or any leads at all that we could follow? Any help will be greatly appreciated. Thanks, Elias Hi Elias, Unfortunately, I guess you are right on GP not having an out-of-box solution for this. In the past on one occasion I had to conver...

Good afternoon.I am a newbie to Access but very familiar with Excel. Here is the setup: 3 tables linked to Oracle database 1 query using the 3 tables. Relationships all to one record (Product_No) I have created a query to give me all the info I need except one field required a calculated field which works correctly when I run the query.The problem I am having is that when I enter ">0" in the criteria under the calculated field, I get no records to show and I know there are 314 records before I enter the criteria and 182 are >0.(I counted) One of the records is a sum.I ne...

Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.: In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- Oct-08 In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost. In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550. Another set of data is the "criteria": In cell G7 is the "1-Jan-08" 【it is Starting Date】; in cell G8 is the "1-Dec-08"【it is Ending Date】and in cell G9 is the "Cost" 【it is Category】. I know using the function &...

Alright this one might seem a little odd. Basically when I initialize my dialog I have to call a routine (RunSameThread) which unfortunately calls some functions set with a library (which I don't have the source code for) which may not exit. (Basically this call should initialize the COM port and send's some commands to a transceiver attached to the port, then wait for a response before returning). Now, if the transceiver is not hooked up (or turned on) the COM port initialization will go though, but the library function will sit in an infinite loop :( Thus, I am trying to write a t...

When using data filter advanced. How does one filter, when the criteria wants data from one column only when another column is blank. I have tried ="=""" for the blank column, but it does'nt work??????? -- dougfalcon ------------------------------------------------------------------------ dougfalcon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34432 View this thread: http://www.excelforum.com/showthread.php?threadid=541975 A couple of ways, if you use a header in H1 then in H2 put = if you use a formula, then leave H1 blank and r...

Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

When I use this formula I want it got a huge range of cells but I have particular cells that have a Fill Color in them. Now on the criteria I want it to count the cells that have a fill color in them? I've tried using "Fill Color(Color I'm using)" after the comma in formula but I still get a 0 count when I. I've also tried just doing =countif(range, then just the color) but it doesnt seem to count the cells that have a fill color. Could anyone help me with this? check out this link... http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH....

Hi, 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html In this JEM says that we have double negs so that =SUMPRODUCT(--(A1:A5>10),B1:B5)) can be coerced in to 1. As per JEM"s explanation single unary will coerce True/False to Zero/One and the second double unary is used so that the negative values could be converted to its original sign. My "reasoning" was instead of using double negative sign why not use a single + sign and achieve further speed increase. So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used was ...

Hi I have just changed form Lotus Notes to Outlook 2007 and need to include my company Logo at the top of all emails, automatically. How do I do this in Outlook 2007 so it is included as an imbedded image and not as an included jpg so it is not removed by the receivers email client. thanks Daniel you need to create stationery but there is no way to prevent the receivers email server from removing it or blocking it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mail...

I'm trying to do a similair process to the lookup function, but the dat isn't sorted, and due to some cross sheet links, I am unable to sor the data. So I'm looking for a function to find the correct company i one range and then return the correct multiplier in the correspondin range... I will comment my function with missing code Function FindReturn(Company) Set Foundcell = ActiveSheet.Range("B62:B69").Find_ What:="end", LookIn:=xlValues, LookAt:=xlWhole) If Foundcell Is Nothing Then FindReturn = 1 Else r = Foundcell.Row ' FindReturn = value in cell wit...

I am brand new to using the charting feature of Exel and am having trouble with what should be a simple operation. I want to create a graph of y=f(x) -- say for simplicity's sake f(x) = x^2. I have in column A, for x, the numbers -10 through +10 and in column B =A^2. Column B shows the correct values but have not been able to create the pretty graph I know so well. Would someone give me a bit of help here? Thank you very much. Where do you get stuck? Select the data range, click the Chart Wizard. In step 1 select the XY Scatter chart. That should give you what you want. ...

I have an e-mail contact list that I need to filter. Column A = E-mail Address Column B = Company Name Alot of people from the same company may be on the list, so in Column B there will be alot of repetition while the co-responding values in Column A would be unique. Sometimes a company name will be listed but we don't have the e-mail address, so the value in Column A would be blank. Sometimes a company name will be listed with both e-mail addresses and blank values (in Column A). I'd like to do a few things: 1) Filter the list so that all dupliate company names ...

I used to be able to have messages in my Outbox sit there till I hit send/receive by checking "disable send/receive" on the tools menu. For some reason, disabling send/receive now only disables the automatic receive function. My emails in my Outbox are sent automatically, as soon as they hit the outbox. I send monthly emails to my contacts thru the mail merge program in Word. I then go to my Outbox to attach a document to them. It used to be that if I disabled the automatic send/receive function in the tools menu, the messages in my outbox would sit there so I could add ...

Hi I have a range A1:S1 with a series of integers and a range A2:S2 with another series of integers. How can I count all the instances where the equivalent cell in the second range is equal to two less than that in the first range. eg 6 4 9 7 8 etc 4 3 7 8 7 etc the above would count 2 - Columns A and C Thanks in advance Sandy =SUMPRODUCT(--(A1:S1-A2:S2=2)) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/...

Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Hi! I have a problem with my report about Accounts Receivable. It is supposed to show all the Accounts Receivable, regardless if whether they are paid or not. Now, the problem is that in the textbox "Total", it should only include those which are not paid,which could be determined by the checkbox "Paid" which is also shown in the report. If there is a check on the box,it means that the account has already been paid. The textbox "Total" sums the value from the textbox "Amount". The values are from the same query, What criteria should be in placed and wh...

I'm trying to design a spreadsheet that sums up total numbers worked fo a week and then compares that total value to 40 to see if any overtim hours were worked. The problem is that since the times that I' comparing are in HH:mm format, excel isn't recognizing when I try t compare it to the value of 4 -- Message posted from http://www.ExcelForum.com Hi Cheizm! Format your sum [hh]:mm:ss Compare to 40/24 Or multiply your sum by 24 and compare to 40. Note that time is handled by Excel as a decimal part of a day so you compare to 40/24 or multiply by 24 before comparing. See:...

Hi- need to get a formula which will count the number of times 2 seperate criteria are met- firstly that in column G the entry is Alicia, and secondly that in column I there is an entry- have tried to piece this together myself but as only an occassional user i'm struggling!! Thanks C Also takes into account if someone touched the space bar in i2:i22 =SUMPRODUCT((G2:G22="alicia")*(LEN(TRIM(I2:I22))>0)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "clare_s" <clares@discussions.microsoft.com> wrote in message new...