Excel: compare 2 columns and, count and return value 3rd column

Question

I want to compare the data in column A and B in excel. For every cell in B that matches any cell in A I want to take the corresponding value in C and add this up. Then I want to get the average of these cells in C.

Example: 
A       B       C      D
jan     jan     8      apple
piet    kees    7      apple
bas     erik    9      pear
jan     piet    2      kiwi
tom                    kiwi
lies                   apple
jan                    pear
sam                    lemon
kees                   pear

In the example above I would want the outcome to be 5.66666 if you consider column A, B and C. The numbers in column C that correstpond with 'jan', 'kees', and 'piet' in column B are averaged, because the names in column B also are listed in column A.

Additionally I would want to also add in column D. How can I also add that I only want the people in A that have 'apple' listed in column D. So the outcome in the example above would be 7.5

Would you mind refining your explanation please? I can't understand the requirement — Yassin Hajaj
– Yassin Hajaj, Commented May 12, 2018 at 22:24

user4039065 · Accepted Answer · 2018-05-12 22:41:57Z

1

Put this array formula somewhere and finalize it with ctrl+shift+enter rather than just enter.

=AVERAGE(IF(COUNTIF(A1:A9, B1:B4), C1:C4))

answered May 12, 2018 at 22:41

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Ivonne Jansen-Dings Over a year ago

Thanks Jeeped! This worked like a charm. I'm trying to add another criterium, but not very succesful ;) I might get back to you later for some more help :)

Ivonne Jansen-Dings Over a year ago

Maybe you know the answer: If I list fruit in column D, which show the fruit that the people in column A have eten, how can I then only average the numbers in column C for which the names not only match, but who have only eaten lets say an apple. - I added this example above, hopefully to clarity my question.

BassSultan · Accepted Answer · 2018-05-12 22:29:39Z

0

a simple google search revealed the function COUNTIF(range,condition) that returns 1 if your B is somewhere in A.

I would go with =IF(COUNTIF(A_start:A_end,B_i),C_i,0) and sum the range in C

answered May 12, 2018 at 22:29

BassSultan

7141 gold badge6 silver badges11 bronze badges

Collectives™ on Stack Overflow

Excel: compare 2 columns and, count and return value 3rd column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related