Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default SumProduct Function

I am using Excel 2007. I have it looking at dates and a couple requirments
in the formula. N$1 is my date of 01/03/2008. The change details tab lists
all of the records. When I use this formula for a monthly report (using date
above January, 2008), it pulls the data correctly. But I need it for daily
activity. Below is my formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),1)),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

For example it came back as 3 records for the month of January. When I put
01/03/2008 into cell N$1 and change my formula (see below), it doesnt work.
Below is the changed formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),DAY(N$1))),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

Am I getting something wrong? I comes back as 0 records

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 171
Default SumProduct Function

The first part of your function is forcing the comparison date on the
Change_Details sheet to be the first of the month:
Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1
If you want to compare actual dates the formula gets a lot simpler, as you
don't have to adjust the Change_Detail date. You also don't need to break N1
into components (year, month, day) just to put them back together:
=SUMPRODUCT(--(Change_Details!$A$2:$A$1499=$N$1),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))
(I'm assuming that N1 and the dates in Change_Details!A:A are JUST dates,
with no time specified. If that's not true, you'll need to calculate away
the time components)


"mp80237" wrote:

I am using Excel 2007. I have it looking at dates and a couple requirments
in the formula. N$1 is my date of 01/03/2008. The change details tab lists
all of the records. When I use this formula for a monthly report (using date
above January, 2008), it pulls the data correctly. But I need it for daily
activity. Below is my formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),1)),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

For example it came back as 3 records for the month of January. When I put
01/03/2008 into cell N$1 and change my formula (see below), it doesnt work.
Below is the changed formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),DAY(N$1))),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

Am I getting something wrong? I comes back as 0 records

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default SumProduct Function

If N1 = 1/3/2008 .....

The only difference between the 2 formulas is that the first one is "looking
for" 1/1/2008 and the second one is "looking for" 1/3/2008.

--
Biff
Microsoft Excel MVP


"mp80237" wrote in message
...
I am using Excel 2007. I have it looking at dates and a couple requirments
in the formula. N$1 is my date of 01/03/2008. The change details tab
lists
all of the records. When I use this formula for a monthly report (using
date
above January, 2008), it pulls the data correctly. But I need it for
daily
activity. Below is my formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),1)),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

For example it came back as 3 records for the month of January. When I
put
01/03/2008 into cell N$1 and change my formula (see below), it doesn't
work.
Below is the changed formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),DAY(N$1))),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

Am I getting something wrong? I comes back as 0 records



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default SumProduct Function

More info:

The only difference between the 2 formulas is that the first one is
"looking for" 1/1/2008 and the second one is "looking for" 1/3/2008....


Which will never meet the condition since you're manipulating the dates to
be the first of the month.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
If N1 = 1/3/2008 .....

The only difference between the 2 formulas is that the first one is
"looking for" 1/1/2008 and the second one is "looking for" 1/3/2008.

--
Biff
Microsoft Excel MVP


"mp80237" wrote in message
...
I am using Excel 2007. I have it looking at dates and a couple
requirments
in the formula. N$1 is my date of 01/03/2008. The change details tab
lists
all of the records. When I use this formula for a monthly report (using
date
above January, 2008), it pulls the data correctly. But I need it for
daily
activity. Below is my formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),1)),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

For example it came back as 3 records for the month of January. When I
put
01/03/2008 into cell N$1 and change my formula (see below), it doesn't
work.
Below is the changed formula.

=SUMPRODUCT(--(Change_Details!$A$2:$A$1499-DAY(Change_Details!$A$2:$A$1499)+1=DATE(YEAR(N$1), MONTH(N$1),DAY(N$1))),--(ISNUMBER(SEARCH($L$3,Change_Details!$Q$2:$Q$1499) +(SEARCH($L$1,Change_Details!$B$2:$B$1499)))))

Am I getting something wrong? I comes back as 0 records





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMPRODUCT or another function??? Paul Groth Excel Worksheet Functions 2 May 7th 08 02:30 PM
sumproduct function FPJ Excel Worksheet Functions 5 May 7th 07 10:04 PM
SumProduct function Sam Excel Worksheet Functions 2 March 10th 07 05:43 AM
Sumproduct Function JimMay Excel Discussion (Misc queries) 3 June 17th 06 11:24 PM
SUMIF Function Inside SUMPRODUCT Function Abdul Waheed Excel Worksheet Functions 17 September 19th 05 04:24 PM


All times are GMT +1. The time now is 06:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"