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...Difference between 2 Opportunities reports
Can you help me to understand the difference between 2
1. Pipeline Chart report by sales stages
2. Pipeline Chart report forecast by sales stages
...reporting tools #2
What reporting tools do you all like? I have looked at a couple but would
like to know what you all recommend?
I'd recommend Quest MessageStats.
"Brandon" <email@example.com> wrote in message
> What reporting tools do you all like? I have looked at a couple but would
> like to know what you all recommend?
...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...Excel Chart #2
I am developing a chart on Excel. I have two columns
with numbers. A third column has a formula to average
the two numbers. I want to graph the average. Problem
is my graph looks bad. If I enter the two numbers, they
are averaged in the third column and graphed correctly.
The problem is Excel interprets rows that have not been
entered as zero and graphs them as zero.
I would like rows that have not yet have data entered
into them to be treated as if there is no data and thus
not be graphed.
Hope someone can help me? I need this for a project I
working on to graph the pH of ...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...Date Subtraction #2
I run Win2K with Excel 2K.
I would like to enter a date in a cell (eg Oct-05) and have the preceding 11
cells automatically put the previous months in.
In cell A15 = Oct-05 (entered)
In cell A14 = Sep-05 (automatically)
In cell A13 = Aug-05 (automatically)
In cell A12 = Jul-05 (automatically)
etc etc etc
Is there a formula that can do this?
Any help will be much appreciated...thanks !
On Wed, 9 Nov 2005 16:34:57 -0800, "John Calder"
>I run Win2K with Excel 2K.
>I would like to ent...MX Records #2 #2
I would like to setup an MX record #2 of weigh 20 for another location - not
on same domain or even same network. The goal is to hold mail in case that
primary MX record / server is down. How do I setup the SECOND server for
this setup? Thank you.
On Fri, 21 Apr 2006 12:41:55 -0500, ">>Smith<<" <firstname.lastname@example.org>
>I would like to setup an MX record #2 of weigh 20 for another location - not
>on same domain or even same network. The goal is to hold mail in case that
>primary MX record / server is down. How do I setup the SECOND server for...Using 2 versions of Mac Office 2004 (Japanese and English). How do I set the default for which version my machine uses to open office files?
I using 2 versions of Mac Office 2004 (Japanese and English). How do
I set the default for which version my machine uses to open office
files? It would of course be better if MS allowed users to switch
menu languages within the program just as Mac does with its OS and
associated software. But since that is not the case, I have had to
purchase install the English version of Office 2004 (at great expense
both monetarily and memory wise). When I open files already on my
computer it always opens them with the "default" version of Office
2004 which is in Japanese. I can't seem to ...Fixed Allocation #2
We have about 40 departments. Currently, we allocate several expense items to
these departments based on pre defined percentages.
For example: 7130 Telephone is allocated to 40 Departments
7140 Utilities are allocated to 40 departments
7150 Office supplies are allocated to 40 departments.
We have about 12 expense accounts that are allocated to the 40 accounts. The
percentage table is same for all accounts and is updated once a year.
My question is Becauase I have 12 expense accounts that allocated to 40
departments would I need to set up 12 fixed a...All Day events cover 2 day
When I create an all day event or even an event that covers several days, it
looks fine in monthly view if you look at it in a list view it spans over to
two days. So when you sync your calendar your pda shows the event as 2 days.
The default for an all day event is 12:00 am to 12:00 am is there a style
sheet that can be changed to reflect an all day event to be 23 hours and 59
Add the duration field to the view and you'll see they are only 1 day. If
they spread over 2 days on the pda, check the time zone settings on the pda.
Diane Poremsky [MVP - Outlook]
Out...Multiple IF Condition
I posted the following in worksheetfunctions.. but I understand this NG
is not active so posting it here again.
I am using Windows XP/Office 2003 and have the following problem:
I have a worksheet with 8 Columns as follows:
A B C D E
F G H
S# Ref. No Date CODE Company Problem Status ClosedDate
I wish to test two Cells viz. Column C (Date) and Column H
If the date entered in Date is greater than or equal to Today(), the
value displ...How do I draw multiple arrowheads on the same line?
I am creating a flow chart with many boxes connecting into one. Because the
connectors merge together before reaching the destination box, my co-workers
want to see multiple arrowheads along the length of each connector so they
can tell the direction of the flow is toward the box and not back up one of
the other connectors.
AFAIK the flowchart routing style will always cause dynamically glued
connectors to go to the centre of the line (of the bounding box).
If you use static glue then you can connect to connection points.
Therefore, you add more connection points to your flow chart shap...Exchange Calendar #2
If we schedule a meeting, no one can see anything on
anyones calendar past 3/31/2004. Is there a setting to
...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...Need recommendation for a high-speed barcode label printer suggestion #2
Currently we mainly use Zebra 2824 printers and they work great with
RMS. We now have the need for a higher speed printer (2824 not cutting
for the volume) - one that is a single label feed (1.25 x 1 is fine)
AND the ability to autocut between each sku that's sent to the printer
from RMS (this may not be possible with RMS...I do not know...part of
what I'm trying to find out). Any suggestions or someone out there
doing something similar? Thanks!
...Need help with data validation format
I'm trying to make this format mandatory when someone enters his/her
data in a cell.
The format would be this : "### ###". Therefor, if the worksheet user
doesn't enter a chain like this one : "113 244", the data just won't
enter. And yes, the space beetween the 3 first and last numbers has to
I can't seem to find anything in the Data / Validation menu that
enables me to do this :confused:.
Any help would be much appreciated. Thanks in advance.
~~ Message posted from http://www.ExcelTip.com/
~~...Hiding multiple addresses in an e-mail
I want to be able to send an e-mail to multiple
addresses, or a group address that I have set up.
My question is this: How can I do this so each
individual that receives the e-mail will only see their e-
mail address, or the e-mail Group name?
There are two ways you can approach this. You could create a new contact
with a name like "Hidden Recipient List" and assign your e-mail address to
it, then put that contact in the To field and all the other addresses in the
BCC field. When the recipient gets the message, he/she will see only
"...Offline store & multiple profiles
a customer of us has some employees that work at multiple locations.
These locations all have their own SBS server, making trusts between
Because of this they multiboot, several employees can boot their laptop
in 2 installs, one for location X, the other for location Y. On both
locations they need outlook configured to access the location X exchange
server. This isn't such a problem, but the offline store replication is,
as some mailboxes are insanely large.
Is there a possibility (or a better solution) in which we can have 2
user profiles, but only one outlo...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"
..."To" Field #2
is it possible to change the contents of the "to" field of the message in my
outlook mail box?
Any insight would be highly appreciated
Many thanks in advance
Can you try to explain a bit further?
Which Mailbox do you mean? Inbox, sent items etc. Or do you mean when
making a new email?
In what situation do you want to change the contents of To... field?
Judy Gleeson - MVP Outlook
Acorn Training and Consulting
see what Outlook training can do to improve productivity:
www.acorntraining.com.au/...Reprot builder 2.0 how to use URL access parameter
i will create a link button in my system, when the user click the link, it
will redirect to reporting services to open a report, for example the link is
which CompanyID is my parameter i want pass to the report. When the report
load how i can receive this parameter and filter the data column
CompanyID=DEMO only will appear in the report.
...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
...validation for decimals
I need to set a series of cells so that the user cannot enter anything
other than 2 decimal currency values. Here's my code for this:
..Range("K" & sI & ":K" & sI).Validation.Add Type:=xlValidateDecimal,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0.00",
It works but it still allows the user to enter more than 2 decimal
places. There is no setting that I can find to limit the user so
he/she cannot enter 3 or 4 or 5 or more decimal places.
I know I can foramt the cells t...