Using Sumifs To Check Multiple Criteria On The Same Column

[iamhttp]

Hey guys.. I've been talking these pages for a few weeks now and decided to join up

I humbly ask for some minor help in something that i just can't wrap my head around..
the story is this :
I've got two sheets, one is a database type sheet with a bunch of transactions with all their relative information and one is a sheet that tries to summerize the information depending on the criteria you feed it.

sheet 'transactions' holds the database
sheet 'item list' holds the table.
sheet 'information' holds some data for various arrays i use, the most important is an array called all = { jenny, amarr}
(the list is taken from the 'information' sheet)


=SUMIFS(Quantity,Item,item_list,Sold_Bought,D3,mon th,B1,Char,INDIRECT(B2))

this is the formula i have, some basics:
Trying to sum the quantity on these criteria
Item matching the type of item in the list(in 'item list')
Sold_bought and D3 is to check if i bought or sold that item
month and B1 is to sum by a specific month

B2 holds text that's data validated to {jenny, amarr, all}, with jenny = {"jenny"} for example.
so indirect(B2) refers to an array holding the names that i want to summerize by.

this is as far as i got, if i type in Jenny and Amarr in B2, it works fine.
if i type All it doesn't work.
i've read multiple solutions stating that i need to add a sum in the start of things, so i'll have :

=Sum(SUMIFS(Quantity,Item,item_list,Sold_Bought,D3 ,month,B1,Char,INDIRECT(B2)))
but when i hit ctrl+shift+enter i get the same number for each item type.
in this case, i get the number 10,289,272. no clue why!

So my questions are these:

1)What exactly does sumifs do in when checking an array of conditions? does it sum up everything by condition A, and then everything by condition B?(given A and B are in the same column)
2)why doesn't my code work properly?
3)how could i have figured this out alone without bothering you guys?

any help is much appreciated.

[Excel Fox]

hi iamhttp

Welcome to Excel Fox

What exactly does sumifs do in when checking an array of conditions? does it sum up everything by condition A, and then everything by condition B?(given A and B are in the same column)

SUMIFS as you understand does a conditional sum on the sum range based on the criteria you passed for each criteria range. The thing with passing an array of values in the criteria is that in such cases one will have to sum up the individual array results, which is why you've read else that you need to add a SUM before the SUMIFS. The SUM acts as an aggregation for the individual results of the SUMIFS function

2)why doesn't my code work properly?

Your equation SUMIFS(Quantity,Item,item_list,Sold_Bought,D3,mont h,B1,Char,INDIRECT(B2)) needs the extra aggregation using SUM function

3)how could i have figured this out alone without bothering you guys?

You probably would benefit by some bit of reading. But it's always better to follow forums where experienced Excel experts share their knowledge on the subject, and especially on formulas (both array and non-array). And I'm sure the good ones out there wouldn't mind you 'bothering' them.

[iamhttp]

Hey thanks for the reply


Adding the sum to aggregate the sumif doesn't work, hence my posting.

I understand that sumif sums for one specific criteria, and then the sum() function basically aggregates all the conditions.

However, when i do add the sum(sumif()), the formula 'breaks', and every single result in "bought" or "sold" would be the same number.(this is after hitting CSE).
so, despite the fact i understand your explanation to a high degree, it still doesn't work.
and i'm still pretty clueless as to why.(as i said, even when i add the Sum function)

thanks again, Iamhttp

[Excel Fox]

That's because your aggregation is happening on all of your item_list. That's not what we want, right. So you'll need to pass only those criteria that you specifically need, ie, the individual item.

Here's how you do it

D4=SUM(SUMIFS(Quantity,Item,$C4,Sold_Bought,D$3,mo nth,$B$1,Char,INDIRECT($B$2))) as array formula

or

D4=SUMPRODUCT(SUMIFS(Quantity,Item,$C4,Sold_Bought ,D$3,month,$B$1,Char,INDIRECT($B$2))) as regular formula

drag this down and across and you should see results

[iamhttp]

to be more specific: as i've found this out by some more tweaking;

It seems that sumif, when used with this array condition, returns the condition of the first position in the array;

we if assume the array looks like this
Jenny
Amarr

the sumif function works like this:
Item 1 --- summed by condition = jenny
item 2 --- summed by condition = amarr
item 3 and above--- not summed

this leads me to believe something is moving where it shouldn't be
That's not what i an array acts like inside a sumif. really strange(for me a at least.)

[iamhttp]

That indeed did the trick..
I guess i still don't really understand how arrays work
but thanks alot for your help, seems like i got some more reading to do before i keep touching arrays!