 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2005sumProduct (gimme a 1 if this is true, gimme the value) what is th
SUMPRODUCT(--(DATA!$C$2:$C$1500=98366), DATA!$AB$2:$AB$1500) This is what I have been trying to use. What I am doing is looking for a specific zip code in one column when I find it I want the value of another column to be added to a total. Scott Miller University of Washington Chemistry As it is, think your formula should work. If it's somehow not returning the correct sums (or zeros), then the problem could be either that the (some) zip codes in col C are text numbers, and / or that (some) numbers within the col to be summed, col AB, are text numbers One way is to try instead: =SUMPRODUCT(--(TEXT(Data!$C$2:$C$1500,"00000")="98366"), --Data!$AB$2:$AB$15 00) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- > SUMPRODUCT(--(DATA!$C$2:$C$1500=98366), DATA!$AB$2:$AB$1500) > [quoted text clipped - 5 lines] > University of Washington > Chemistry All I get with the current code is a dash (Cell looks like this----->[ - ] )  SignatureWhen I used the text idea cell looks like [ #value ] but I expect a certain value that I have calculated. Scott Miller University of Washington Chemistry > As it is, think your formula should work. If it's somehow not returning the > correct sums (or zeros), then the problem could be either that the (some) [quoted text clipped - 24 lines] > > University of Washington > > Chemistry > All I get with the current code is a dash > (Cell looks like this----->[ - ] ) Think the above is probably just a zero, display is due to cell formatted as "Accounting" If you re-format the cell as "General" or "Number", then the zero would show > When I used the text idea cell looks like [ #value ] > but I expect a certain value that I have calculated. Try the full alternative expression suggested to your next response -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- This is the exact code I am using: =(SUMPRODUCT(--(DATA!$C$2:$C$1500=98366), --(DATA!$AB$2:$AB$1500))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98367), --(DATA!$AB$2:$AB$1500)))/$B$417 SignatureScott Miller University of Washington Chemistry > As it is, think your formula should work. If it's somehow not returning the > correct sums (or zeros), then the problem could be either that the (some) [quoted text clipped - 24 lines] > > University of Washington > > Chemistry This alternative expression could probably be refined further, but think we could try: =SUMPRODUCT((TEXT(Data!$C$2:$C$1500,"00000")="98366")+(TEXT(Data!$C$2:$C$150 0,"00000")="98367"), --Data!$AB$2:$AB$1500)/--$B$417 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- > This is the exact code I am using: > =(SUMPRODUCT(--(DATA!$C$2:$C$1500=98366), > --(DATA!$AB$2:$AB$1500))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98367), > --(DATA!$AB$2:$AB$1500)))/$B$417 =SUMPRODUCT(--(ISNUMBER(MATCH(Data!$C$2:$C$1500,{"98366","98367"},0))),--Data!$AB$2:$AB$1500)/--$B$417 Biff > This alternative expression could probably be refined further, > but think we could try: [quoted text clipped - 14 lines] >> --(DATA!$AB$2:$AB$1500))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98367), >> --(DATA!$AB$2:$AB$1500)))/$B$417 "Biff" wrote : =SUMPRODUCT(--(ISNUMBER(MATCH(Data!$C$2:$C$1500,{"98366","98367"},0))),--Dat a!$AB$2:$AB$1500)/--$B$417 It's a good refinement, Biff. But going by the same tack that there could be a mixture of real/text numbers within col C, think a slight adjustment would be: =SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(Data!$C$2:$C$1500,"00000"),{"98366","9836 7"},0))),--Data!$AB$2:$AB$1500)/--$B$417 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- What you describe is what SUMIF does. The general format of that function is =sumif(where_to_look,what_to_look_for,what_to_add). In your example, =sumif(DATA!$C$2:$C$1500,98366,DATA!$AB$2:$AB$1500). If, by chance, your zip codes have been entered as text, you'll have to look for them as text, so you'd change 98366 to "98366" > SUMPRODUCT(--(DATA!$C$2:$C$1500=98366), DATA!$AB$2:$AB$1500) > [quoted text clipped - 5 lines] > University of Washington > Chemistry |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.