Summing using criteria for columns and rows

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.
 
D

David

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top