Sequentially Increment a Text Field Based on Select Criteria
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:
In Table FISCAL-YEAR I have a field called FY and there is one record which
In the sample above, I expec...Sumproduct
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
even I dublicate date column(G) and change the formula to :
but it dosen't work again, any idea to solve this problem would be
I don't see anyting wrong with your formula.
Instead of multipl...Sumproduct or??
If I have two columns of numbers:
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
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...SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri
shall be OR? I.e. something lik
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
equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...Function that meets table requirement then multiplies by table fac
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:
A b c
Sector P&L w/o Admin P&L w/ Admin
In column C I would like to put a formula that; if column A mactches column
A in table reference th...If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to
count a range of data for charting.
I am using the following formula:
It works great....except...
In column $M$3:$M$189 there are also
blank cells and I want to count these a...Convert (change) Functional Currency
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
Unfortunately, I guess you are right on GP not having an out-of-box solution
In the past on one occasion I had to conver...Calculated Fields and criteria
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...Sumproduct or ?????
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
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"
...LIST out all the data according to the "Criteria"
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-
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 &...Problem writing Time-Out 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...Advanced criteria
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'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...Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
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...=Countif(range, criteria)
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
Could anyone help me with this?
check out this link...
HTH....Increasing the speed of Sumproduct
1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html
In this JEM says that we have double negs so that
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
...Include logo in emails
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.
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...find function #4
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
Set Foundcell = ActiveSheet.Range("B62:B69").Find_
What:="end", LookIn:=xlValues, LookAt:=xlWhole)
If Foundcell Is Nothing Then
FindReturn = 1
r = Foundcell.Row
' FindReturn = value in cell wit...Plotting functions
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) =
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.
...Filter duplicates based on criteria / column values
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 ...Disabling automatic send function
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 ...Sumproduct??
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
6 4 9 7 8 etc
4 3 7 8 7 etc
the above would count 2 - Columns A and C
Thanks in advance
=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/...Conditional formatting formula with multiple criteria
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!
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...REport Criteria?
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...comparing hours using if function
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
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:...Count if 2 criteria are met
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
Microsoft MVP Excel
"clare_s" <email@example.com> wrote in message