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

[Guest]

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

 

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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"

 

[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

 

[Max]

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

 

[Biff]

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


Biff

 

[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