Help Adding up data from a table

[skate1991]

Hi,

I was looking for a bit of help with a problem I have,

Please see the attached worksheet,

I have done 3 sets of data validation lists in cells B33-B35

I’m looking to be able to change any of the lists and for it to work out the correct sums, I have tried using sumifs but I couldn’t get it to work,

So for example I would like to be able to change the name to alex the type to F2F and the month to March and for it to add up the relevant cells from the table.

Thanks

[benishiryo]

hi skate1991, you may try this:

Please Login or Register  to view this content.

[skate1991]

Hi Benishiryo,

I tried copying that formula to cell C33 and its coming back with a circular ref error,

Any Ideas.

Thanks

[benishiryo]

i used column C to count the number of rows you have. so copying this formula into C will have problems. does this range always change? if it does not, you can use:
=SUMIFS(OFFSET($B$1,1,MATCH($B$35,$C$1:$L$1,0),29),$A$2:$A$30,$B$33,$B$2:$B$30,$B$34)

or if it is dynamic & you need your formula in column C, do the count in some other column. i will use L for eg:
=SUMIFS(OFFSET($B$1,1,MATCH($B$35,$C$1:$L$1,0),COUNT($L:$L)),$A$2:$A$30,$B$33,$B$2:$B$30,$B$34)

[Vinodsralian]

Hi Skate,

Why not you add a pivot?


Vinod

[skate1991]

Thanks To both of you both solutions work.