designing an array

[DUAL]

=IF(AND(G42>29%,G42<40%),K42,0)

I am taking numbers from G column that are equal to 30 through 39 %
Then I am taking the numeric value from K column and printing it out.

Having a problem getting this to work as an array. I tried doing this

=IF(AND(G42:G99>29%,G42:G99<40%),K42:K99,0)

and I am just getting a zero returned.
What I want to do is take each value from K column for each value in G that is between 29-40% and add them all up. hope that makes sense.

Values in G Column range from 20 to 49. I just want to take those rows that have between 29-40 in G column, and then take their K column values and add them together.

Any idea how to do this? I've tried everything I can think of. Thanks a lot.

[EdMac]

Hi Dual

try

Please Login or Register  to view this content.

[DUAL]

Hi Dual

try

Please Login or Register  to view this content.

thanks, that works great

i must have been tired when i posted because i forgot to mention that i also have a C column that contains either "HOME" or "AWAY" that i need to discern between

im not sure exactly how to do it but i guess i need an IF statement in there somewhere?

thx for the help all

[NBVC]

Add the condition with...

e.g.

=SUMPRODUCT((G42:G99>29%)*(G42:G99<40%)*(C42:C99 ="Home"),(K42:K99))

to include only items with "Home" in column C

[DUAL]

Add the condition with...

e.g.

=SUMPRODUCT((G42:G99>29%)*(G42:G99<40%)*(C42:C99 ="Home"),(K42:K99))

to include only items with "Home" in column C

Perfect, you guys are awesome. thanks a lot.

[DUAL]

Add the condition with...

e.g.

=SUMPRODUCT((G42:G99>29%)*(G42:G99<40%)*(C42:C99 ="Home"),(K42:K99))

to include only items with "Home" in column C

I have a similar request for help here, been trying to figure this out on my own for a while tonight with no luck.

I am pretty sure I need a sumproduct formula here.

=SUMPRODUCT(('MLB Picks'!B42:B9999="ARI"),('MLB Picks'!M42:M9999),('MLB Picks'!Q42:Q9999))

I'm trying to pull the team name (ARI) from column B, then if the team name is a match, add the numbers in columns M and Q together, and spit the number out in the cell.

I keep getting zeroes back for responses, and this is definitely wrong. I should be getting real numbers back from the other sheet 'MLB Picks'

thanks for your help everyone, you have been a lifesaver!

[NBVC]

You need to force the first conditional array to 1's and 0's...as it is now, they return only True's and Falses...add double negatives or multiply the first array by 1 or add 0 to the first arrray...they all work

E.g. Try:

=SUMPRODUCT(--('MLB Picks'!B42:B9999="ARI"),('MLB Picks'!M42:M9999),('MLB Picks'!Q42:Q9999))

Note... this sums the products of the corresponding values in M and Q...is that what you really want?

By your description you may want...

Please Login or Register  to view this content.

[DUAL]

You need to force the first conditional array to 1's and 0's...as it is now, they return only True's and Falses...add double negatives or multiply the first array by 1 or add 0 to the first arrray...they all work

E.g. Try:

=SUMPRODUCT(--('MLB Picks'!B42:B9999="ARI"),('MLB Picks'!M42:M9999),('MLB Picks'!Q42:Q9999))

Note... this sums the products of the corresponding values in M and Q...is that what you really want?

By your description you may want...

Please Login or Register  to view this content.

strange, on the first formula i am just getting a zero returned (like i mentinoed before)

on the 2nd formula I am getting a !VALUE error like i was before with the formula i was trying

thanks