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. I
can't make it work. I guess my mistake is that DATES=MAX(DATES) is no
related to NAME=CARLOS. And Because the NAME and the MAX DATE doesn'
match I retrieve no value.

Any ideas will be very much appreciated.

Thanks and regards,
Carlos

--
Message posted from http://www.ExcelForum.com

0
7/1/2004 12:15:55 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
443 Views

Similar Articles

[PageSpeed] 52

   MAX(IF(NAMES="Carlos",DATES))
returns the latest Carlos date.  It will require array entry despite the 
use of SUMPRODUCT.

Jerry

litos_aldovea < wrote:

> 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 CARLOS
> 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. I
> can't make it work. I guess my mistake is that DATES=MAX(DATES) is not
> related to NAME=CARLOS. And Because the NAME and the MAX DATE doesn't
> match I retrieve no value.
> 
> Any ideas will be very much appreciated.
> 
> Thanks and regards,
> Carlos.

0
post_a_reply (1395)
7/1/2004 8:27:19 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...

Access 2007 12-16-07
Why was no classic view included in access 2007? When I migrated to xp, I was pleased I had a classic option. Windows vista has less classic options than it should in my opinion, but it was still there. Even when IE 7 came out, I could still see the classic menus. But in Access 2007 (which cost us almost $500 and only came in the most expensive office), didn't allow this. I understand others desires for different looks and feels, but what has always brought me back to microsoft is that I could always get some semblance of what I was used to. I am deeply distressed by the fact t...

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

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

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

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

analytical Accounting #16
I have two dimensions as Dim1 and Dim2. Dim2 is a Child of Dim1 in terms of Reporting and analysis e.g. Dim1 has one to many links to Dim2 e.g. Dim2a, Dim2b, Dim2C. How can i link Dim1 and Dim2 to extract a Report analusing dim1 and the corresponding Dim2s? e.g. Grant1 = Dim1 and within Grant1 have multiple Projects below it as Dim2 e.g. Project a, Project b, and Project d. I want to report Grant 1 and the details in the Projects. Thanks ...

READ 11-16-05
-- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" ...

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

Public Folders #16
Is there an easy way to move the Public Folders from an Exchange 5.5 server to a new install Exchange 2003 server, in a similar fashion to creating a PST from one server and moving it to another? ...

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

opening attachments #16
i opened a new message and dragged a few emails in as attachments (as a backup) when i saved it, it saved as a doc file. now when i try open it, it opens in Word as a blank document (without the attachments) how do i get the attachments back? "john" <john@discussions.microsoft.com> wrote in message news:CF4B4F90-28E8-4B57-A5C5-083299248678@microsoft.com... >i opened a new message and dragged a few emails in as attachments (as a > backup) when i saved it, it saved as a doc file. now when i try open > it, it > opens in Word as a blank document (without the att...

Need ODBC driver for FoxBase 16-bit on XP
I need to import into Excel on a Windows XP machine with Office XP data from an applicaton that is written on a 16-bit version of FoxBase (not Visual I guess). When I try to set up a ODBC connection I get a message that I have to go to msdn.microsoft/vfoxpro and download it. On that site I find ODBC driver for Visual Fox 6 with references to Windows 98 and NT. What am I supposed to use? It might be named FoxPro, not FoxBase, but I'm offsite now so I haven't got the chance to check. ...

COUNTIF #16
I want to count how many numbers in a range are less than a given value. However, I don't want that given value to be hardcoded in the COUNTIF formula. For example: I want this formula: =COUNTIF(P4:P1073,">=-0.0045") to look something like: =COUNTIF(P4:P1073,">=V4") However that second formula doesnt seem to work. Any ideas? =COUNTIF(P4:P1073,">="&V4) -- HTH RP (remove nothere from the email address if mailing direct) "adamcal" <adamcal@discussions.microsoft.com> wrote in message news:C152B7A1-3388-422A-8383-4EC3...

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