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

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

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

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

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

"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(--(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

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