Sum rows based on criteria on both columns and rows

[wysie218]

Hi, I need to sum rows based on criteria on both columns and rows. How do I do that? For example, I want the sum of sales for stores that are of type "SIS" and are comp "Y" for weeks 1-4. See attached example file. Thank you.

 

[prasaddn]

hi,

See if this works.

Regards,
Prasad DN

 

[wysie218]

Hi Prasad, thanks, but it does not work. I need to add that I cannot change the structure of the file. To the right I've added examples...any of the criteria can change (no. of weeks, comp, type) and other than manually adding it up I'm not sure how to do it.

 

[Nebu]

Hi:

Please find the attached. I have used the helper columns to get the desired results.

Thanks

 

[Asheesh]

Hi -

One more option...

Try below in the attachment in #4

SUMPRODUCT(($B$5:$J$56)*($B$2:$J$2=$O5)*($B$3:$J$3=$P5)*($B$4:$J$4=Q$4)*($B$1:$J$1=$N5)*($A$5:$A$56>=MID($M5,SEARCH(" ",$M5),SEARCH("-",$M5)-SEARCH(" ",$M5))*1)*($A$5:$A$56<=RIGHT($M5,LEN($M5)-SEARCH("-",$M5))*1))

 

[Hui]

Wysie

After trying Asheesh's solution, you may want to have a read of:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[wysie218]

Thank you so much for all the responses...trying them out now.