Sumif based on row and column criteria

[Hornet75]

Hi this is my first post on here so please be gentle!

like this

week no 1.1 1.2 1.3 1.4

cat

1 14 19 11 12

2 7 10 10 10

1 10 4 7 9

2 6 7 6 9


I want to add up all instances of category 1 in week 1.1 say, so the result should be 24.

How can I right a formula to do this? I want the result to be on a summary page on another worksheet.

Many Thanks for your help.

 

[Luke M]

Something like this:

=SUMPRODUCT((B1:E1="1.1")*(A2:A5="Category 1")*(B2:E5))

 

[Martin]

Luke,


I thought on the same, and then saw your comment...


the problem with that is the transposition of one of the arrays. If you Evaluate the formula, you'll see it clearly.


that said, The fastest solution I can think of is a =SUMIF(A2:A5;1;B2:B5), for each week and cat.


nevertheless, I'm eager to figure out the matrix solution of it....


Hope this helps.

 

[Luke M]

Martin,


Not sure what the problem is exactly...formula is working okay in my workbook. As for transposition of arrays, that's needed, not a problem. Can you show example where problem occurs?

 

[NARAYANK991]

Hi Martin ,


I think it is to do with your data types ; on my computer , Luke's formula with a slight change for these , works.


=SUMPRODUCT((B1:E1=1.1)*(A2:A5=1)*(B2:E5))


The weeks have been entered as numbers ; so also the category.


The entire formula is an array formula , to be entered with CTRL SHIFT ENTER.


Narayan

 

[Hui]

Hornet75


Firstly, Welcome to the Chandoo.org Forums


You may want to have a look at the post: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[Hornet75]

Hi Guys!

Many Thanks for your responses. NARAYANK991 's solution works just great!


Now I am very pleased to be creating a model that actually works!

Thanks Again

Kevin