Sumif using or

[gepeacock]

Data that looks like this

Account May June July ...
1 $10 $15 $22 ...
2 $12 $8 $34...
....

Each account number is distinct (no duplicates)

I want a forumula to give me the sum of certain accounts for may.

I can use sumif(a2:a200,1,b2:b200)+sumif(a2:a200,2,b2:b200) to get
totals of account 1 and 2 for may but what if I wanted to sum 10
accounts for may that would be a really big formula... there must be a
better way.

Something like sumif(a2:a200,or(1,2,3,4,etc),b2:b200) which excel
doesnt allow.

Seems to me that vlookup with an array would work but I cant get it to
sum the results.

Thanks for any help
Gary

 

[Peo Sjoblom]

Since the account numbers are distinct you can use

=SUMPRODUCT((A2:A10={1,3,4})*(B2:B10))

will sum accounts 1,3 and 4 in B2:B10 (May)
if you want to copy across to get the sum for June use

=SUMPRODUCT(($A$2:$A$10={1,3,4})*(B2:B10))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[gepeacock]

Thats for the quick reponse... its exactly what I needed.

I might be asking too much but what about making it a bit more user
friendly by putting the critera in a seperate cell
=SUMPRODUCT(($A$2:$A$10=criteracell*(B2:B10))

where somebody could enter something like 1,3,4 into the criteracell

Otherwise I will just use search and replace when I want to change the
critera in all the formulas I create.

Thanks again
Gary

 

[Peo Sjoblom]

That would be a problem but if they could enter it in (in this example) 3
cells across

=SUM(($A$2:$A$10=J1:L1)*(B2:B10))

or using 3 cells in the same column

=SUM(($A$2:$A$10=TRANSPOSE(F1:F3))*(B2:B10))

both formulas needs to be entered with ctrl + shift & enter

or maybe easier if you use the same number of accounts

=SUM(($A$2:$A$10=TRANSPOSE(F1:F10))*(B2:B10))

then you can just type in the accounts in F1:F10




--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon