Summing using criteria for columns and rows

  • Thread starter Thread starter John
  • Start date Start date
J

John

Here is my problem:
I have 100+ rows of data with different unique letters in
them (all repeated numerous times). I have 50+ columns
that have different names in them (some repeated as
well). What I like to do is sum the values that
correspond to certain row and column criteria.
For example:
Apple Orange Pear Apple Banana Orange
A 50 100 5 10 15 20
B 20 20 25 30 35 40
C 30 3 6 9 12 15
A 100 5 8 10 15 80
A 70 80 20 20 10 15

So- how can I write a formula to sum every time there is
a row of "A" and a Column of "Apple" matching? In this
example, the solution would be 260.
Thank you.
 
Apple Orange Pear Apple Banana Orang
50 100 5 10 15 2
20 20 25 30 35 4
30 30 6 9 12 1
100 5 8 10 15 8
70 80 20 20 10 1
349 405 64 349 87 40
{=SUM(IF($A$1:$F$1=A1,$A$2:$F$6,0),0)} ----> array formula in A7, but the formula shows Apple twice, once in A7 and in D7. Might be able to run the "Down" instead of across, so you won't duplicate the names, then you could do a grand total

Thanks
Davi
 
Back
Top