I have a function:


Which looks at cell A8 on sheet 'Matis' and checks what it is against a series of cells on the sheet the function is written, and returns a value based on what it finds.

For example, it looks at Matis!A8, and sees that it is equal to B11, and returns 8.

I'm trying to make it such that the function looks down from say A8:A100 on 'Matis' and sums up the result for each cell.

For example if it looked at A8 and returned 8, A9 and returned 10, A10 and returned 10, the function would return 8+10+10=28.

Currently I just print all the results to a different column by dragging the formula down, then sum that column, but I'd like all this to be done in one cell

I've seen SUMIF, SUMIFS and SUMPRODUCT around, but I'm not sure which one, if any of them I should be using here. Anyone got any hints? I hope I explained this well enough.


You can make use of a simple IF formula with a combination with an array input. Screenshot attached below.


  • Cells A1:A10 contain data that need to be compared with cell F1 (input value)
  • Corresponding value to be taken if matched is present in B1:B10


  • Setup an IF formula against the entire range A1:A10, and if true return the range B1:B10

  • Embed this IF statement inside a SUM formula and use Ctrl+Shift+Enter to let excel know that this is to be treated as an Array formula.

enter image description here

  • In your example, my function would look at B1 and compare it to A1, if A1 had 'Good' written in it and B1 has 'Good' written in it, it returns 8, anything else returns 10. Then (this is the part I can't do), it looks down the rest of the B column, checking the contents against A1 and returning 8 or 10 dependent, while keeping a cumulative sum which the function returns at the end. – Tom Mar 20 '18 at 13:19
  • With the scenario as per your comment, you can try this =SUM(IF(A1=B1:B10,8,10)) and enter it as an array formula. This should suffice. – Bharat Anand Mar 20 '18 at 21:32
  • Ok thanks, that works for trying to do only one argument of A1, I've tried to expand it to work on multiple, i.e =SUM(IF(OR(A1=B1:B10,A2=B1:B10),8,10))) but this doesn't seem to check individually, rather if ANY of B1:B10 are equal to EITHER or A1 or A2, 8 is returned for all values of the sum. For example: if A1=Blue,A2=Green and B1=Orange,B2=Green,B3=Black. The value returned from the function is 8+8+8=24 rather than the correct value of 10+8+10=28, – Tom Mar 20 '18 at 23:40
  • Check out this link, it may come to your rescue, else I will give it a go later tonight - stackoverflow.com/questions/28617944/… – Bharat Anand Mar 21 '18 at 5:41
  • Try this =SUM(IF(A1:A2=TRANSPOSE(B1:B3),8,10)). With this code, you can now iterate over the items in column A. Do not forget to enter it as an array formula – Bharat Anand Mar 22 '18 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.