Index, Match, Sum ??

[fastballfreddy]

Ok, I'm trying to figure this out, but not there yet. I think I need
some help.

In Cells A2:A32 there are values: 1,2,3,4,5 or -
In Cells AT2:AT32 I have total values from each row.

In a table I want to find the totals of 1, 2, 3, 4, 5 and -

So cell T49 would = the SUM for 1, T50 = SUM for 2 and so on.

I need a formula that will search A2:A32, get all that have 1 and then
go to AT for each of the ones that have 1 and sum the values.

So lets say A4, A15, A17 come back as having 1
It would then sum the values of AT4, AT15, AT17.

I'm thinking I will need to nest INDEX, MATCH, & SUM to my results, but
haven't been able to figure it out. Any ideas?

Thansk

 

[Bondi]

Hi,

Try something like this in Cell T49

=SUMPRODUCT((A2:A32=1)*(AT2:CAT32))

Regards,
Bondi

 

[fastballfreddy]

thanks for the reply,

that would work, only problem is on the off days the total N/A, so the
result of the formula is #VALUE!

I can't really change my other formula for N/A, (it's complicated) but
it's involved in a couple other formulas

 

[fastballfreddy]

I got it figured out, easier then expected

=SUMIF(A2:A32,1,AT2:AT32)