sumProduct (gimme a 1 if this is true, gimme the value) what is th

S

Scott

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
 
M

Max

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)
 
S

Scott

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
 
S

Scott

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
 
B

bpeltzer

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

Max

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
 
M

Max

Scott said:
All I get with the current code is a dash
(Cell looks like this----->[ - ] )
Click to expand...

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.
Click to expand...

Try the full alternative expression
suggested to your next response
 
B

Biff

=SUMPRODUCT(--(ISNUMBER(MATCH(Data!$C$2:$C$1500,{"98366","98367"},0))),--Data!$AB$2:$AB$1500)/--$B$417


Biff
 
M

Max

:=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
 
Top