Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif using or
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif using or
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif using or
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif using or
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 wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |