Sumproduct with multiple date criteria
Having a tough time with this one.
Column A = Start Date, Column B = End Date, Column C = Quantity.
Row A = Start Date, Row B = End Date.
I would like Row C to sum quantity from sheet 1 where ever the two date
The date ranges on sheet 2 represent the beginning and ending of a week
Column A Column B Column C
01JAN2010 24JAN2010 1,000
Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010
Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010
Row c 1,000 1,000 1,000 0
Do the Sheet 2 Star...sumproduct 12-29-09
Hi, I have a format as below, but it seems that <>"h" is not a correct
expression when I want to choose all rows except those including "h" in
Thanks a lot!
That will exclude all rows in J2:J20000 that contain *only* the single
Is that what you intended?
Microsoft Excel MVP
"Lowan Chan" <LowanChan@discussions.microsoft.com> wrote in message
news:30EA9061-F2...SUMPRODUCT to substiute array formula
I have this formula
entered as an array formula, to sum a column even if some of the cells
contains error values.
Now I wonder if it could be done using SUMPRODUCT or any other functions, to
avoid the array formula?
Try this (normally entered):
Hope this helps.
On Feb 26, 8:42=A0am, "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk>
> I have this formula
> =A0 =A0 =A0 =3DSUM(IF(ISERROR(A1:A100),0,A1:A100))
> ente...Forecasting using SUMPRODUCT and dates
I am having a problem with counting data that is within a certain date.
for example, I have 4 columns. SERVERNAME, LOCATION, TYPE
LEASEENDDATE. I am currently using SUMPRODUCT to count the number o
servers I have for a particular location and type.
A B C D
SERVERNAME LOCATION TYPE LEASEENDDATE
Server1 MN Web 6/30/2004
Server2 CA App 8/15/2004
On another sheet, I would like to forecast of how many servers I hav
that are not expired...SumProduct basics
I'm trying to get started with User-Defined Functions. For the sake
of learning, I want to create a UDF that acts same as the built-in
SumProduct function. So, the two arguments would be two ranges,
range1 and range2. The first cell.value in range1 would be multiplied
with the first cell.value in range2 and so on - and then all added
together at the end. I thought I'd start by using a for loop to go
through each cell in range1 but now I'm not sure what is the best/most
efficient method to pair the values in range1 with their corresponding
values in range2. How should t...SumProduct not right
This formula is summing ALL the incidences in the N column of what's in E2. I
want it to sum ONLY those that are in E2 but also also matching what's in A3.
I can't figure out why it's not working, because a similiar formula works
for other situations.
On Fri, 8 Jan 2010 13:49:01 -0800, Steve
>This formula is summing ALL the incidences in the N column of what's in E2. I
>want it to sum ONLY those that are...SUMPRODUCT() yielding #NUM!
Bob Phillips & Glen helped me out with a SUMIF() question I posted earlier
today. Both of you recommended that I use this formula:
(I changed the name of the range from Date to PayDate so I don't use a
reserved function name.)
Now I am getting a #NUM! errror. I looked in help and it said this happens
when you have non-numeric data. I've gone through the entire columns in
question (PayDate and LWOP) and deleted the values from all cells that should
be blank. I just have numbers, dates and the column headings. ...sumproduct!!!
In the first sheet, i have the following data
S INR 2000 17.6
P SAR 300 30.6
P USD 100 38.4
P USD 200 76.8
S GBP 100 71.5
S GBP 200 143
S EUR 100 47
P AED 200 20.9
In the second sheet it is to be sorted by formula, not by pivot table
i made a formula but it is giving wrong result, if somebody helps i
this matter much obliged.
the formula i tried i
I want the result is 300. I know instead of the SUM something els
thanks in advance
Message posted from http://www.ExcelForum.com
I have 3 columns of data, NAME, DATE,VALUE
NAME Date Text
Carlos 1/1/4 10
Carlos 2/1/4 20
Carlos 5/6/4 30
Carlos 6/6/4 40
Peter 5/5/4 50
Peter 20/6/4 60
The forumla that I need to use is
Look for CARLOS in the database, and retrieve VALUE when NAME is CARLO
and DATE is the LATEST (for carlos of course).
In this example if I look for Carlos the data I want is 40
If I look for Peter the data I want should be 60
The formula I tried was
I think it is because it doesn't match the latest date for carlos. ...sumproduct #2
This looks sooo right to me, but gets an error #VALUE! Not sure where
it's wrong. Any help would have me a lot more hours staring at the
=SUMPRODUCT(DUMP!A:A=A13)*(DUMP!B:B=$A$1)*(DUMP!E:E) ==> #VALUE!
I have tried re-writing the formula as:
=SUM(IF((DUMP!A:A=A13)+(DUMP!B:B=$a$1),1,0)*DUMP!E:E)==> 0 (but should
be a value greater than 0)
The value A13 exists in Dump, column A, more than once.
The value A1 exists in Dump, column B, more than once.
Numerical data exists in Dump, column E.
What am I doing wrong??
"Carole Kaufman" <email@example.com&g...Sumproduct accross multiple sheets
I'm using the formula above to calculate some stats for a call center.
On a master "group summary page", I need to get the sum of the result
of this formula from 20 worksheets. Other than duplicating the formul
and changing the names, is there a faster way to do this? The cel
references are identical for all, worksheets just the names change.
The only ...array formulas-sumproduct and average
Hello, I need hel. I am using this formula
=SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong
with it. I need to find the average for column H in relation to 1(under 2
yrs) in Column E. Hope this makes sense.
Array-enter the formula with CTRL+SHIFT+ENTER
GMT+8, 1� 22' N 103� 45' E
"Becky" <Becky@discussions.microsoft.com> wrote in message
> Hell...SUMPRODUCT Help 12-02-09
I apologize for my 3rd question w/in two days, but every time one thing
works, another thing does not.
In our survey- we are trying to find data in regard to our students (who are
in different programs) and what classes are beneficial. Our students can
identify themselves in up to 3 different programs out of 6 total programs
(coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different
beneficial classes (Columns P, Q, R). Each row is a different student. The
How many students who identified as '2' also said class 'x' is beneficial?
...SUMPRODUCT with two criteria?
I am SUMPRODUCT to count the number of items in a list that are
"green", except for those which are also "bad". It looks like this:
Unfortunately, "bad" in F3:F100 can be green, red, or blue!
How can I adjust this so I get:
= (the number of "green" in C3:C100) - (the number of "green" in
C3:C100 with "bad" in F3:F100)
What functions do I need to make this work?
In the 2nd SUMPRODUCT just add another arr...Sumif or Sumproduct 2 criterias not working
I used the following formula:
The first part looks for "Calibration", then I need it to look for the month
"Jan" and when it finds those two to calculate C3:C88.
I think my issue is with the field "MONTH" that contains a formated versus
("Jan"), off the column next to it with "DATE" i.e. 01/05/04 - is it not
recoginizning "Jan" as a word or does it still look at this field as a date,
if so h...Help with complex SUMPRODUCT formula
Getting this SUMPRODUCT forumula to work has me stumped.
For range: $E$3:$E$352 is not blank
For range: $S$3:$S$352 is equal to or greater than $P$3:$P$352 *2 (cells
where P is => 2x S)
I've come up with this formula, but clearly it does not work:
Additionally, I need a derivation of this forumula which will count (rather
than sum) the number of cells in S which meet the first two criteria.
I tried this, but it does not work:
=SUMPRODUCT(--($E$3:$E$352<>0),--(($...Spin Box Date and Sumproduct
Is it possible to use a date (created via an INDEX calculation) within a
I use a SUMPRODUCT calculation to generate headcount figures off monthly
master sheets as per the following:
However, I would like to now change the date source of the data based on a
month name generated by a Spin Box so that when the month changes fropm Apr09
to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So
how do you join the '$O$1:$O$6000' part to the cell containi...sumproduct in code
I am using code to try and populate a table.
The table looks like
I am using code to put a sumproduct in AB2 and AB3 (and further as
Currently the line of code I am using is:
cells (r+1, "ab").formula = "=sumproduct((" & causecats.address & "=" &
cells(r+1, "aa") & ")*(" & cause.columns(3).address & "))"
When I look at what is showing up in cell AB2, I see:
The ranges are correct. The problem appears to be that the value,...SUMPRODUCT with conditions 04-28-10
I have a formula which is working as follows:
This basically counts the numbers of values in a column against a set value.
So for me it counts the number of projects that have dates against the
I need to amend this to only count the value if the phase is currently in
progress. This would basically be if D4:D52 < Today() and E4:E52 > Today().
Unfortunately I'm having great difficulty putting this in to a formula.
Could someone please assist?
Tha...SUMPRODUCT Question #4
I have a SUMPRODUCT with multiple criteria and wish to add another
condition using OR ,,,,,, e.g.
A B C D
Pens 18/10/2005 ABC 25
Pens 18/10/2005 BCD 10
Pens 18/10/2005 DEF 15
What I want to achieve is to sum the product in col A that matches the date
in col B
and has a location ( col C ) of either ABC OR BCD and sum col D.
What do you mean by a sum of non-numeric data?
would count th...Sumproduct #25
In sheet1, I have 2 columns, column A is weeks numbered 1:52, & column B are
numbers. Ex: column A38 is the week number "38", column B38 has "25".
My report is sheet2. When I enter a week number in A1, I want the
corresponding number from sheet1, column B (in this example, "25") to display
in column B1. No matter what week number I enter, I need the correct
corresponding week number from sheet one. Thanks for any help.
I presume 'Ex' meant Example, and '38' was in Cell A38 and '25' was in
In cell B1 of Sheet2 pu...Problems with sumproduct and dates
I'm a newbie in sumproducts but have found it very functional. However
I have a probelem with using a data as criteria in an array. The formul
doesn't work when testing for the date (in the example 18-10-2004), bu
when I test the formula with anything else - e.g. a text string - i
works well. Do you have a solution?
Thanks in advance
Kreller's Profile: http://www.excelforum.com/member.php?action=getinfo&user...SUMPRODUCT Question...
I think a quick question here...
I have a rather lengthy SUMPRODUCT formula that I want to leave a blan
cell if the product of my criteria is zero. Is there a way to do this?
It seems to me that I have seen folks post here about a IFISERROR o
something like that, but I thought that was to return a blank i
VLOOKUP #N/A result. This is pretty similar but I can't seem to figur
out how to get it done.
PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2348
V...Sumproduct with "contains" variable
When you filter a column in Excel 2007, there is an option of "contains" to
choose. Is it possible to use "contains" in a sumproduct() formulae as one
of the arrays or do I have to list the possibilities in each of the
variables? Surely there must be a way to use wildcard characters of ? or *
or something to pass this on in the formulae.
If so, how?
> When you filter a column in Exce...Another SumProduct Question
Here's a simple example of something I'm trying to figure out.
Can I calculate (5*4)+(7*9)=83 using a sumproduct?
I tried this but it doesn work for what I think is an obvious reason.
SumProduct(((B1:B4)*(A1:A4=1)),((B1:B4)*(A1:A4=2))). I believe this isn't
working because there is never a case where A1:A4=1 AND A1:A4=2. Therefore I
get the answer = 0.
I'm also having a hard time trying to figure out how I would do this in a
loop of some kind as an alternative. Can I define B1:B2 as a range and B3:B4
as a range and pass them to a...