SUMIF & OR

N

Neil Pearce

Dear all,

I wish to use a SUMIF(range, criteria, sum range) forumula utlising OR for
the criteria, e.g.

=SUMIF(A1:J1,OR(A10:A20),B2:J2)

It would appear however that this does not work. Can you please point out
the error of my ways, thank-you.


Yours in appreciation (as always),

Neil
 
B

Bernard Liengme

Can you tell us in words what you want?
Is it: to sum B values when corresponding A value matches anything in
A10:A20?
best wishes
 
V

vezerid

Oops, one more parenthesis in the end:

=SUMPRODUCT(SUMIF(A1:J1,A10:A20,A2:J2))

HTH
Kostis
 
N

Neil Pearce

Hi Bernard,

That is indeed the situation that I require solving.


Cheers,

Neil
 
B

Bernard Liengme

I think this is what you want
=SUMPRODUCT(--(A1:J1=TRANSPOSE(A10:A20)),A2:J2)
best wishes
 
B

Bernard Liengme

I was too quick to hit Send:
1) this is an array formula and needs CTRL+SHIFT+ENTER
2) the vector A10:20 has 11 elements while A1:J1 and A2:J2 have only 10 each
so use =SUMPRODUCT(--(A1:J1=TRANSPOSE(A10:A19)),A2:J2)
best wishes
 

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

Similar Threads

Sum up 2 or more columns under same SUMIF criteria 2
SUMIF - pointing to another cell for criteria 3
sumif 9
Sumif with two criteria including a date range 4
Criteria Syntax in SUMIF formula 4
sumif when criteria range is larger but includes criteria 1
sumif function 2
AND statements in a SUMIF (or a lookup) 2

Top