Hi
I am looking for a formula as per the thread title. I have attached an example workbook.
I have been playing around with SUMIFS and SUMPRODUCT but can't crack it.
Formula to sum based on criteria but transposing column to row



Re: Formula to sum based on criteria but transposing column to row
HC
This should be a walk in the park for you
=SUMPRODUCT(($A$3:$A$11=C$17)*($C$1:$E$1=$A18)*($C$3:$E$11))
Take care
Smallman

Re: Formula to sum based on criteria but transposing column to row
Thank you very much Smallman You're right should have been a walk in the park but I just couldn't crack it.

Re: Formula to sum based on criteria but transposing column to row
Sumproduct formula is easy Just remember to break it down into segments. Don't mix numbers and text in the same column and keep your ranges the same size. Once you do that the formula goes like a dream.
Take it easy
Smallman

Re: Formula to sum based on criteria but transposing column to row
use this
=SUMIFS(INDIRECT(ADDRESS(ROW($C$3),COLUMNS($A$2:$B$2)+MATCH($B18,$C$2:$E$2,0))&":"&ADDRESS(ROW($C$11),COLUMNS($A$2:$B$2)+MATCH($B18,$C$2:$E$2,0))),$A$3:$A$11,C$17)


Re: Formula to sum based on criteria but transposing column to row
Thank you pangolin
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!