Sumproduct?

Hello world,

I am working with a xls log function in a filetransfer workflow. The
worksheet is like this:


Date/Time            Job name           Files in job
01-11-2007           Test01.jpg                 1
02-11-2007           Test02.jpg                 1
02-11-2007           Test03.jpg                 1
03-11-2007           Test04.jpg                 1
04-11-2007           Test05.jpg                 1

My goal is to process this data in another worksheet. So far I have
succeeded in having Excel adding the numbers in the "Files in job"
column and displaying the sum in the second worksheet - yes, wow! ;-)
Whats next is to have Excel add the amount  of files transferred for a
given timespan, display this in the second worksheet along with a
graph illustration. Say I want the number of files transferred
november or maybe the past week - is this possible?
I have been on google for the past hours looking for a tutorial or
something to help me construct this function but until now my
desperate fiddle in Excel with sumproduct has been in vain..

Sincerly yours,
Rasmus Olsen
Denmark

0
ro (24)
11/14/2007 4:22:29 PM
excel 39879 articles. 2 followers. Follow

19 Replies
678 Views

Similar Articles

[PageSpeed] 12

=SUMPRODUCT(--(YEAR(A2:A50)=2007),--(MONTH(A2:A50)=11),C2:C50)

or something like this in Danish

=SUMPRODUKT(--(�R(A2:A50)=2007);--(M�NED(A2:A50)=11);C2:C50)


Where A2:A50 holds the dates and C2:C50 the files in job

for the past week use


=SUMPRODUCT(--(A2:A50>=TODAY()-7),C2:C50)

or in Danish

=SUMPRODUKT(--(A2:A50>=IDAG()-7);C2:C50)

the latter requires that your pc clock is up to date and I assume there 
cannot be any file transfers in the future


-- 


Regards/M.v.h.


Peo Sjoblom




<ro@dxp.dk> wrote in message 
news:1195057349.014352.230880@19g2000hsx.googlegroups.com...
> Hello world,
>
> I am working with a xls log function in a filetransfer workflow. The
> worksheet is like this:
>
>
> Date/Time            Job name           Files in job
> 01-11-2007           Test01.jpg                 1
> 02-11-2007           Test02.jpg                 1
> 02-11-2007           Test03.jpg                 1
> 03-11-2007           Test04.jpg                 1
> 04-11-2007           Test05.jpg                 1
>
> My goal is to process this data in another worksheet. So far I have
> succeeded in having Excel adding the numbers in the "Files in job"
> column and displaying the sum in the second worksheet - yes, wow! ;-)
> Whats next is to have Excel add the amount  of files transferred for a
> given timespan, display this in the second worksheet along with a
> graph illustration. Say I want the number of files transferred
> november or maybe the past week - is this possible?
> I have been on google for the past hours looking for a tutorial or
> something to help me construct this function but until now my
> desperate fiddle in Excel with sumproduct has been in vain..
>
> Sincerly yours,
> Rasmus Olsen
> Denmark
> 


0
terre081 (3244)
11/14/2007 5:32:16 PM
November

=SUMPRODUCT(--(MONTH(date_rng)=11),files_rng)

last week

=SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng)

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<ro@dxp.dk> wrote in message 
news:1195057349.014352.230880@19g2000hsx.googlegroups.com...
> Hello world,
>
> I am working with a xls log function in a filetransfer workflow. The
> worksheet is like this:
>
>
> Date/Time            Job name           Files in job
> 01-11-2007           Test01.jpg                 1
> 02-11-2007           Test02.jpg                 1
> 02-11-2007           Test03.jpg                 1
> 03-11-2007           Test04.jpg                 1
> 04-11-2007           Test05.jpg                 1
>
> My goal is to process this data in another worksheet. So far I have
> succeeded in having Excel adding the numbers in the "Files in job"
> column and displaying the sum in the second worksheet - yes, wow! ;-)
> Whats next is to have Excel add the amount  of files transferred for a
> given timespan, display this in the second worksheet along with a
> graph illustration. Say I want the number of files transferred
> november or maybe the past week - is this possible?
> I have been on google for the past hours looking for a tutorial or
> something to help me construct this function but until now my
> desperate fiddle in Excel with sumproduct has been in vain..
>
> Sincerly yours,
> Rasmus Olsen
> Denmark
> 


0
bob.NGs1 (1661)
11/14/2007 5:34:45 PM
On Nov 14, 6:32 pm, "Peo Sjoblom" <terr...@mvps.org> wrote:
> =SUMPRODUCT(--(YEAR(A2:A50)=2007),--(MONTH(A2:A50)=11),C2:C50)
> Where A2:A50 holds the dates and C2:C50 the files in job
> for the past week use
> =SUMPRODUCT(--(A2:A50>=TODAY()-7),C2:C50)

Thank you for your reply Peo. If I copy/paste your form in my workbook
I get the "The formulay you entered contains an error" dialogue. If I
press ok for assistance in entering the form Excel places the cursor
at the comma right after the '=2007)'. If I delete this comma the
error dialogue returns and places the cursor at the next comma. When
the last comma gets deleted Excel reports back "Excel found an error
[...] Do you want to accept the correction proposed below? Press yes
and Excel enters '=SUMPRODUCT(--(YEAR(A2:A50)=2007)--
(MONTH(A2:A50)=11)*C2:C50)' and I end up with the value '21' in the
cell?

>I assume there cannot be any file transfers in the future

The workflow is running 24/7/365 so I need to have Excel "see" the
entire column.

BTW. I have uploaded my workbook to <ftp://freecall:dxpfree@ftp.dxp.dk/
excelfiddle/joblog.zip> (4K) in case you want to test.

Sincerly yours,
Rasmus
0
ro (24)
11/15/2007 9:06:16 AM
On Nov 14, 6:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> November
>
> =SUMPRODUCT(--(MONTH(date_rng)=11),files_rng)
>
> last week
>
> =SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng)

Mr. Phillips, thank you for your reply. As with Peos form suggestion
it apperars that Excel wont accept the commas. Am I doing something
wrong when entering these forms?

Can you elaborate a little on how to set the ranges?

Sincerly yours,
Rasmus

0
ro (24)
11/15/2007 9:23:57 AM
On Nov 15, 10:06 am, r...@dxp.dk wrote:
> BTW. I have uploaded my workbook to <ftp://freecall:dxpf...@ftp.dxp.dk/
> excelfiddle/joblog.zip> (4K) in case you want to test.


ftp://freecall:dxpfree@ftp.dxp.dk/excelfiddle/joblog.zip (4k)


0
ro (24)
11/15/2007 9:25:29 AM
On Nov 15, 10:25 am, r...@dxp.dk wrote:

> ftp://freecall:dxpf...@ftp.dxp.dk/excelfiddle/joblog.zip (4k)

My apologies,

ftp.dxp.dk/excelfiddle/joblog.zip
user: freecall
pass: dxpfree

0
ro (24)
11/15/2007 9:28:38 AM
Try using semi-colons ( ; ) as you may have a continental edition of Excel.

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<ro@dxp.dk> wrote in message 
news:a326be02-9172-442a-bc06-6a470903f820@c30g2000hsa.googlegroups.com...
> On Nov 14, 6:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> November
>>
>> =SUMPRODUCT(--(MONTH(date_rng)=11),files_rng)
>>
>> last week
>>
>> =SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng)
>
> Mr. Phillips, thank you for your reply. As with Peos form suggestion
> it apperars that Excel wont accept the commas. Am I doing something
> wrong when entering these forms?
>
> Can you elaborate a little on how to set the ranges?
>
> Sincerly yours,
> Rasmus
> 


0
bob.NGs1 (1661)
11/15/2007 12:34:28 PM
On Nov 15, 1:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Try using semi-colons ( ; ) as you may have a continental edition of Excel.

Yup, that was it. Peos form works fine now but yours returns "#NAME" -
I believe I have to define the range?

Sincerly yours,
Rasmus Olsen
0
ro (24)
11/15/2007 12:51:29 PM
Yes, mine was generic, you need to substitute the actual ranges in there, or 
used named ranges.

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<ro@dxp.dk> wrote in message 
news:eef0fc8e-485b-404a-9425-2d0abbd7bfec@i37g2000hsd.googlegroups.com...
> On Nov 15, 1:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> Try using semi-colons ( ; ) as you may have a continental edition of 
>> Excel.
>
> Yup, that was it. Peos form works fine now but yours returns "#NAME" -
> I believe I have to define the range?
>
> Sincerly yours,
> Rasmus Olsen 


0
bob.NGs1 (1661)
11/15/2007 5:34:20 PM
My Danish version used semicolon but you might have an English version with 
Danish
regional settings


-- 


Regards,


Peo Sjoblom



<ro@dxp.dk> wrote in message 
news:eef0fc8e-485b-404a-9425-2d0abbd7bfec@i37g2000hsd.googlegroups.com...
> On Nov 15, 1:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> Try using semi-colons ( ; ) as you may have a continental edition of 
>> Excel.
>
> Yup, that was it. Peos form works fine now but yours returns "#NAME" -
> I believe I have to define the range?
>
> Sincerly yours,
> Rasmus Olsen 


0
terre081 (3244)
11/15/2007 5:36:51 PM
On Nov 15, 6:36 pm, "Peo Sjoblom" <terr...@mvps.org> wrote:
> My Danish version used semicolon but you might have an English version with
> Danish
> regional settings

Hi Peo, a little help modifying your form to look at the entire A and
C column? I have tried the A:A and C:C but receives #NUM.

Sincerly yours,
Rasmus
0
ro (24)
11/16/2007 1:54:16 PM
On Nov 15, 6:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Yes, mine was generic, you need to substitute the actual ranges in there, or
> used named ranges.

Im a little thick - please bare with me ;-) Is it possible to have the
form look at entire columns and not a defined range? Of course I can
use A2:A1000 but there must be some kind of command for this..

Yours sincerly,
Rasmus Olsen
0
ro (24)
11/16/2007 2:06:14 PM
No you cannot. SP is a formula working on arrays, and arrays cannot include 
whole columns in pre-2007 Excel.

You could use dynamic ranges

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)

instead of A2:A1000, but you have to be careful that you base all ranges on 
the same count. For instance if you used A2:A1000 and B2:B100 you would use

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)

and

OFFSET(B2,0,0,COUNTA($A:$A)-1),1)

note the count is based upon the same column.

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<ro@dxp.dk> wrote in message 
news:e3e5310c-2715-42c6-a53f-85aa2c137bad@b36g2000hsa.googlegroups.com...
> On Nov 15, 6:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> Yes, mine was generic, you need to substitute the actual ranges in there, 
>> or
>> used named ranges.
>
> Im a little thick - please bare with me ;-) Is it possible to have the
> form look at entire columns and not a defined range? Of course I can
> use A2:A1000 but there must be some kind of command for this..
>
> Yours sincerly,
> Rasmus Olsen 


0
bob.NGs1 (1661)
11/16/2007 5:03:10 PM
On Nov 16, 6:03 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
> OFFSET(B2,0,0,COUNTA($A:$A)-1),1)

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus



0
ro (24)
11/17/2007 12:52:53 PM
On Nov 16, 6:03 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
> OFFSET(B2,0,0,COUNTA($A:$A)-1),1)

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus



0
ro (24)
11/17/2007 12:53:06 PM
On Nov 16, 6:03 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
> OFFSET(B2,0,0,COUNTA($A:$A)-1),1)

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus



0
ro (24)
11/17/2007 12:53:15 PM
Sorry, I had one too many brackets in there

OFFSET(A2,0,0,COUNTA($A:$A)-1,1)

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<ro@dxp.dk> wrote in message 
news:804ceecf-50e7-446c-ac0d-c7ee6ef24697@w73g2000hsf.googlegroups.com...
> On Nov 16, 6:03 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
>> OFFSET(B2,0,0,COUNTA($A:$A)-1),1)
>
> Excel reports back an error. If I press ok it highlights the COUNTA
> part. A reference is displayed: =FORM(reference; rows; cols; [height];
> [width]) - but even though I replace commas with semis and insert
> brackets I get the error message. A little help mr. Phillips ;-)
>
> Rasmus
>
>
> 


0
bob.NGs1 (1661)
11/17/2007 5:23:10 PM
On Nov 17, 6:23 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Sorry, I had one too many brackets in there
>
> OFFSET(A2,0,0,COUNTA($A:$A)-1,1)

Hi Bob, it works now but returns a high value (39387) - could Excel be
adding the dates to the calculation?

Rasmus
0
ro (24)
11/19/2007 9:43:18 AM
What is the whole formula now?

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<ro@dxp.dk> wrote in message 
news:cdd83bc9-d88f-450c-bc52-b93715aa8b4a@b36g2000hsa.googlegroups.com...
> On Nov 17, 6:23 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> Sorry, I had one too many brackets in there
>>
>> OFFSET(A2,0,0,COUNTA($A:$A)-1,1)
>
> Hi Bob, it works now but returns a high value (39387) - could Excel be
> adding the dates to the calculation?
>
> Rasmus 


0
bob.NGs1 (1661)
11/20/2007 10:39:05 PM
Reply:

Similar Artilces:

Sumproduct
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...

Sumproduct or??
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...

SUMPRODUCT and OR?
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...

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: '=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...

Sumproduct or ?????
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 ...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
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...

Increasing the speed of Sumproduct
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 ...

Sumproduct??
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/...

sumproduct
I have this formula in use and it works perfectly. I now need to change the sum area from H4:h484 to H4:J484 and now it only give me 0. How do I get it to sum the larger area?? =IF($B3<=0,"",SUMPRODUCT((SVEexp!$B$4:$B$484=Summary!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry Hi You need to make sure that all of the ranges in SUMPRODUCT are the same size. If you alter one of the ranges, you'll have to alter the others accordingly. Hope this helps. Andy. "Jerry Kinder" <jkinder@jkinder.com> wrote in message news:eeznF54...

Sumproduct #16
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 =sumproduct(NAMES=Carlos)*(DATES=MAX(DATES)),VALUE) I think it is because it doesn't match the latest date for carlos. ...

SUMPRODUCT IF
I need a conditional sumproduct (i.e. "sumproductif") function. Has anyone ever figured out how to do this? Thanks... ...

Is there a way to use a multiple column array in SUMPRODUCT?
I am attempting to use SUMPRODUCT to pull values froma multiple column spreadsheet and add them together. I am able to get the SUMPRODUCT to work if I am only looking for values in one column, but as soon as I select all columns, the function no longer works. example: column A holds accounts, column B holds subaccounts, columns D - F (possibility of over 100 columns) hold companies I am looking for all values to be added together that hold values for both column A and column B =SUMPRODUCT(--(A2:A61=A44),--(B2:B61=B44)--(F2:F61)) works for just column F, but when I try ...

SUMPRODUCT ?
Hi In C4 on Sheet 1 I want to count the number of instances where the text in Data!Z4:Z5000 is the same as Sheet1!A4. I am trying to use SUMPRODUCT, but I think that this is probably the wrong function. Can anyone advise please? Thanks A I'd try this in C4: =countif(data!z4:z5000,A4) Since the formula is in the same sheet as A4, the sheet name doesn't need to be included. Alex Hammerstein wrote: > > Hi > > In C4 on Sheet 1 I want to count the number of instances where the text in > Data!Z4:Z5000 is the same as Sheet1!A4. > > I am trying to use SUMP...

Sumproduct or ????
I need to calculate 'unreconciled amount' in my check register by looking at column B (deposit or withdrawal), column E (check amount), column F (bank balance) and column G (reconciled). Column G will contain either "R" for reconciled or it will be null. All Uncreconciled (no "R") transactions must be examined to see if they are deposits or withdrawals (column B), subtracting withdrawals from deposits. Can anyone help with a formula? I currently have a helper column that is used to total the unreconciled amounts. (=IF($G278<>"R",I...

SUMPRODUCT function
Good Morning All, Using Windows XP & Excel XP I have a worksheet that displays a daily 3 digit lottery number with the 3 digits being separted in column A2, B2 & C2 In columns A1,B1 & C1 is the number picked by a player. Example: A B C D ---------------------------- 1 1 2 3 (Player pick) 2 4 1 7 (Lottery number drawn) 3 3 3 4 (Lottery number drawn) I would like to have a formula that would count the number of occurences that a player number matches a lottery d...

Sumproduct?
Hello world, I am working with a xls log function in a filetransfer workflow. The worksheet is like this: Date/Time Job name Files in job 01-11-2007 Test01.jpg 1 02-11-2007 Test02.jpg 1 02-11-2007 Test03.jpg 1 03-11-2007 Test04.jpg 1 04-11-2007 Test05.jpg 1 My goal is to process this data in another worksheet. So far I have succeeded in having Excel adding the numbers in the "Files in job" column and displaying the sum in the secon...

sumproduct or countif?
F G 4 $34.5 3 $ 23 1 $23 1 $34.5 1 $6.25 2 $23 How do I count the number of instances that are $34.5? The answer should be 5 (Row 1= 4 * 34.5, and Row4 =1 * 34.5) try this that must be array entered using ctrl+shift+enter =COUNT(IF($O$2:$O$22=34.5,$O$2:$O$22)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Paul" <Msr33@TPx12.com> wrote in message news:i0p474579tqed31g089fnksdno319r6tg1@4ax.com... >F G > > 4 $34.5 > 3 $ 23 > 1 $23 > 1 $34.5 > 1 $6.25 > 2 $23 > > Ho...

Solution with SumProduct?
Novice here, I need to have an output of more than just a find/sum of two columns If the below were on columns A,B and C name--activity--hours bob--change--5 bob--change--4 bob--app --4 john--app--3 john--app--5 john--change--2 bill--hardware--3 bill--hardware--1 bill--app--2 bill--app--4 What I would like to do is output how many hours of the app, chang and/or hardware activity did john do but not just in the numerica sense. Looking for John--App--8 then on the next row John--change--2 but I d not want to display a John--Hardware--0 as I do not care about 0 hour for a certain activity. ...

subtotal on sumproduct
I have a Sumproduct formular that i got from off groups but need it to work on filtered Data =SUMPRODUCT((A3:A51<>"")/COUNTIF(A3:A51,A3:A51&"")) Have been trying to us SUBTOTAL(3,OFFSET(INDEX............ But am unable to to get it to work. Could some one please show me what the formular should be. Thanks have you tried SUBTOTAL? Look in the help index -- Don Guillett SalesAid Software dguillett1@austin.rr.com <Sidata@hotmail.com> wrote in message news:1162327929.909939.259340@h48g2000cwc.googlegroups.com... >I have a Sumproduct formular that i go...

Sumproduct?
Good Afternoon Please could someone help.... I have a monster spreadsheet that tracks recruitment numbers and as part of our monthly reporting i need to count various monthly stats The information i need to summarise for each department is: the number of internal candidates that were offered in each month The number of external candidates that were offered in each month the number of internal candidates that started in each month The number of external candidates that started in each month, Sample Data as follows: Department Candiate type offer date Start date Engineering...

Indirect range in SUMPRODUCT?
If I have a formula that defines arange in cell A1 ="column"&"row"&":"&"column"&"row" and another range defined in the same way in B1, I can reference them with =SUMPRODUCT(INDIRECT(A1),INDIRECT(B1)) However I can not insert the formula in A1 instead of the reference to A1 in the SUMPRODUCT(INDIRECT()) function. Is there a way force this? I ask because SUM, COUNTIF, and others will accept the range definition instead of the reference to the range. Of course this won't save me much space, just want it in a couple place...

Sumproduct.......
I have a problem surrounding sumproduct and setting criteria. I need to sumproduct based on row criteria '19:19'. The value for the row criteria is set in an other cell 'E23'. So sum of row 9 and row 17 where 19 is equal to E23. =SUMPRODUCT((OFFSET(A9,,MATCH(E23,19:19,0)-1,(OFFSET(A17,,MATCH(E23,19:19,0)+1)/1)))) Any help greatly appreciated -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24699 View this thread: http://www.excelforum.com/showthr...

SUMPRODUCT #10
Can anyone help me making use of the Formula "SUMPRODUCT" within the below file? http://www.flypicture.com/download/MzcyMjA= Thank you very much Gennaro Very few contributors will download a file from an unknown source, so you might not get much help. Can you describe what it is you are trying to do and give examples of your formula - explain how it is not working (error, wrong result etc) Hope this helps. Pete On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote: > Can anyone help me making use of the Formula "SUMPRODUCT" within the below ...

Average using Sumproduct
Using Excel 2003, I need to find the average age of males who have had a specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have this formula but am getting a total of the ages rather than the average. =AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East 2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East 2010'!$F$3:$F$11<=DATE(2010,3,31))))) Any suggestions would be appreciated. Thank you. "Basenji" wrote: > Using...

Sumproduct Question
=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)) 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? =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000)) 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: =SUMPRODUCT(--($E$25:$E$2000=4), --($F$25:$F$2000=6.5), ...