SUMPRODUCT with Date?
I'm trying to use the formula below, but its not working correctly. On the
Hiring tab, column T has a date formatted as MM/DD/YYYY. I want a formula
that counts the number of hires in a quarter with other criteria. So for
instance if column U = A3 (Cons) and column D = GMT and column T is in 1Q
(Jan thru March). I'll also need the same formula for 2Q (April thru June),
3Q & 4Q.
the formula is still returning all zeroes.....
is this the best way to do this
kkondrat1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=600
View this thread: http://www.excelforum.com/showthread.php?threadid=26650
so also your original formula returns zero?
"kkondrat1" <email@example.com> schrieb im
> the formula is still retur...SUMPRODUCT #15
Can anyone tell me why this formula is giving me a fraction for the
J5:J23 are all percentages. J5 value is 96.67% the rest are all 100%
so I was expecting a result from the formula above of 100% instead I
When I check the formula out I see the SUMPRODUCT returns 18.966666667
rather than 19 that I would have expected.
On Jun 11, 3:09=A0pm, Pete <fell-wal...@hotmail.co.uk> wrote:
> Can anyone tell me why this formula is giving me a fraction for the
Hi. I need to have in say cell A1 a drop down list (which I obtain with the
data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so that
people looking at the spreasheet can select their % desired. BUT, while I
would like the numbers to chosse from in the drop down list to be those
above, I really need them to be divided by 12 i.e. (0%/12), then (5%/12),
then (10%/12), etc.
I thought i cd try to do it with the indirect function but I ma not really
sure what the best way to do it. Is?
I made be going about it the wrong way as well....
I'm simply brain dead this morning and could use a little help.
I have assembled a column of pointers into a table. These pointers are
simply the row number of the selected data in a table. Assuming that this
column starts in A01, I would like to create a formula in B01 that simply
makes B01 equal to the data located in a specified column (use column F for
the example) in the row specified by A01.
I remember doing this before, but it just excapes me this morning.
Thanks in advance for your consideration and help,
Craig Brandt wrote:
...Sumproduct a column where 2 adj text columns contain same value
I'm using a sumproduct formula to ascertain the number of times that a value
between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell in
column B (B3:B26) contains either "Smith", "Draper" or "Jones":
This formula works fine and details the number of entries where the value in
column A is between 0 and 1, and the name in the adjacent column B cell is
Smith Draper or Jones.
What I need to do is the same SUMPRODUCT of 0-1...Indirect function
I struggle to create a macro with INDIRECT function that would jump to
different cells. What cell I want to jump to depends on a currently selected
cell. This means I need to put a relative reference into the function.
Here is my function - I need to replace R1C1 expression with a realtive one
(currently selected cell address with the same behavior as R1C1 expression).
How can I do that?
Application.Goto Reference:= _
Whatever I do I get follo...Sumproduct Question
This above formula achieves the desired result. However, when I add
a fourth column (below) I get a Value error.
I think I'm close...any ideas?
First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too.
Second, do you have any text values in M25:M2000--or any errors in that range?
If you have some text, you can use a different formula to ignore text:
...Type mismatch error using Sumproduct
I've searched through similar topics and tried several variations of code,
but I keep getting a type-mismatch error when trying to use Sumproduct. I am
wanting to use this in several parts of my code. The first instance (which
is also where the error keeps occurring) is below.
TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
long), CliStat as Integer (0, 1, or 99), and the rest as R...SumProduct using greater than
I have the following formula
=SUMPRODUCT(('All Resource Plan'!$B$3:$B$42=Principal)*
('All Resource Plan'!D$3:D$42>0))
Where B3 to B42 on the all resource plan tab have a
variety of roles including principal.
On the same tab d3 to d42 is a range of numbers from -1 to
I need to sum all values associated with a role that are
greater than 0. I was using this formula with an =1 and
it worked fine, however when I changed it to >0 it no
Any help would be appreciated.
First of all, you are counting not summing, secondly the formula works for
me....Adding to a SUMPRODUCT formula
I have the below forumula
However if there is nothing in cell B22 I am getting VALUE appear. So I need
to add to the above formula that if there is nothing in B22 to simply put
nothing in the cell
Thank you in advance
+(SUMPRODUCT((B22=1)*(F22="CURRENT"...Indirect concatenated link doesn't work with UNC
Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.
To make it even more frustrating, if I copy the cell that doesn't wo...Sum Indirect
Struggling with this indirect formula - got #ref error
A4 = sheet number
any one able to assist?
Message posted via OfficeKB.com
"BNT1 via OfficeKB.com" <u19326@uwe> wrote in message
> Struggling with this indirect formula - got #ref error
> exel 2003
> A4 = sheet number
i've been reading up on all the sumproduct related posts, but can't figure
out what's wrong with mine...
this is my formula:
when i look at the insert function arguments box to help define the arrays,
excel returns the right values, i.e.:
for array 1 : false, true, false, false,.. (this is the first condition)
for array 2 : true, true, true, true,... (this is the second condition)
for array 3 : 1, 2, 4, 12, 4, 5,... (these are the values to add up)
so i thought excel would return '2'...Sumproduct Question #3
This forumula doesn't quite do what I want. As it stands it sums the
values in l15:l3000. What I want it to do is count the number of
values in l15:l3000 providing the criteria is met in the first part of
I'm not quite sure how to do that. (l15:l3000 will have some blank
cells if that matters.)
Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS:
> What I want it to do is count the number of
> values in l15:l3000 providing the criteria is met in the first part of
> the forumula.
=SUMPRODUCT(--...Sumproduct Question #1
I have been doing a lot of reading on this function and can usually get it to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the argument?
Bob Phillips explains =sumproduct() in much more detail here:
The bad news is that Bob's site is experiencing problems.
You may want to try every so often to see if it's back up.
> I have been doing a lot of reading on this function an...Struggling with Sumproduct
Reposting as still and issue.
I have a spreadsheet with accounts transaction data where column a = date,
column b = nominal code, Column c = department name, column d = department
number, column e = amount.
I need to query the columns to sum transactions for a particular month for a
particular nominal code, for a particular department.
The department name is only included to identify the department number, and
will not be used.
My initial thoughts are
However it re...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"
Is it possible to nest an INDIRECT function inside of a VLOOKUP?
Here's what I've got so far:
Cell A27: 6-23-2004
I want to replace '6-23-2004' with INDIRECT($A27) so I can autofill
this formula across a large worksheet, and so when I adjust the date
range, it will dynamically update the information PROPERLY according to
the name (row 4 of same col) and date (col A of same row, referring to
a separate worksheet in the same workbook by that name) in the
I can't seem to get the syntax righ...Using SumProduct in VB
I was hoping someone can help me.
I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain criteria (ie
Month or Year). I currently have an excel formula that does what I am looking
for, but I need to convert it to a VB script.
The formula looks like:
Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
the quantity of the items. So this example counts the number of boxes from
January in this data r...Sumproduct Problem (AB)
Hi everyone, can you please sort out this problem for me?
SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Opening
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138)
'Client Opening Stock'!B2:B138 is the Client Name
Client Opening Stock'!C2:AH138 is the Product
'Client Opening Stock'!C2:X138 is the Amount
I belive the formula is not the proper formula, but this is the result I need.
Thank you for your kind help
You need to tell us what the problem is, and what solution you want. Why is
it "not the proper formula...INDIRECT function error
I have a strange problem that I can't work out.
In sheet 1 A1 - A10, I have a list of Surnames.
Sheet 2 to sheet 11 are named according to this list.
(all these sheets have been copied from a Template sheet)
If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
One thing I noticed is that if I manually insert a sheet and reference the
name in the INDIRECT function, the formula works. It doesn't seem to work for
the sheets that have been copied
> If I use =INDIRECT(a1&"!D20") it doesn...SUMPRODUCT() problem
I try to get this formula or someting like it to work.
The problem is in the H-Column. It contains Dates and times like for
instance 26-04-2007 11:36:34
F2 in the formula contains 26-04-2007, and this is where the problem arises.
No values in H-column contains this exact value, so the formula retunrs a
zero. I have tried using VALUE() combined with INT(), and other combinations
on the h-column, but with no result aso far.
How should my formula look if I want to calulate the number of te...SUMPRODUCT not working?
I need to sum a cell range (F2:F90) based on two conditions of two other
cell ranges (D2:D90="XXX" and I2:I90=""), where the parameter of the first
condition may change ("XXX" changes to "MFB", "TGDolfa", "GATV", etc.) and
the second remains constant (I2:I90="").
On one cell I tried the formula:
and it worked as I got an accurate result.
Now the part that is driving me crazy...
When I copied the formula to the cell below and changed the first...sumproduct between 2 ranges
I want to count the number of cells whose values fall between 0.40 and 0.61.
but the result is not the same as when I manually count the number of cells
so the formula is clearly wrong.
how do I fix?
Message posted via http://www.officekb.com
=sumproduct() likes to work with numbers. The -- changes true/false to +1/0.
"Patty via OfficeKB.com" wrote:
> I want to count the number of cells whose values fall between 0.40 and 0.61.
> I did