sumProduct (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 |
sumProduct (gimme a 1 if this is true, gimme the value) what is th
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 -- "Scott" wrote in message ... 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 |
sumProduct (gimme a 1 if this is true, gimme the value) what i
All I get with the current code is a dash (Cell looks like this-----[ -
] ) -- When 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 "Max" wrote: 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 -- "Scott" wrote in message ... 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 |
sumProduct (gimme a 1 if this is true, gimme the value) what i
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 -- Scott Miller University of Washington Chemistry "Max" wrote: 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 -- "Scott" wrote in message ... 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 |
sumProduct (gimme a 1 if this is true, gimme the value) what is th
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" "Scott" wrote: 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 |
sumProduct (gimme a 1 if this is true, gimme the value) what i
This alternative expression could probably be refined further,
but think we could try: =SUMPRODUCT((TEXT(Data!$C$2:$C$1500,"00000")="9836 6")+(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 -- "Scott" wrote in message ... 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 (gimme a 1 if this is true, gimme the value) what i
"Scott" wrote:
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 -- |
sumProduct (gimme a 1 if this is true, gimme the value) what i
=SUMPRODUCT(--(ISNUMBER(MATCH(Data!$C$2:$C$1500,{"98366","98367" },0))),--Data!$AB$2:$AB$1500)/--$B$417
Biff "Max" wrote in message ... This alternative expression could probably be refined further, but think we could try: =SUMPRODUCT((TEXT(Data!$C$2:$C$1500,"00000")="9836 6")+(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 -- "Scott" wrote in message ... 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 (gimme a 1 if this is true, gimme the value) what i
"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 -- |
All times are GMT +1. The time now is 08:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com