• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs multiple criteria (only if criteria cell is available)

R

ravikiran

Member
Hi Gurus,

I have this formula requirement, I am unable to solve. I have a table with raw data and another table (below) with possible combinations of criteria to sum the total.

upload_2016-5-19_15-26-50.png

I need ONE formula to calculate the sum of "Amount" field for all these combination.

I am aware that I can run a pivot and identify the total, but using pivot, I need to select each individual combination, populate the sum, change the combination, populate the sum and proceed until I am done with all the scenarios. But this is going to tiresome and I have several of these combinations from a big dataset.

Is there anyway to write one formula which can ignore a criteria if not available and sum based on the available ones.

Thankyou,

Cheers,
Ravi.
 
Wow! That's what I like about Chandoo... forums. Get your problem solved within minutes :)

@chirayu: Concatenation that is cheeky. Why didn't I remember this.
@Narayan: Exactly what I am expecting. I tried the same but applied IF condition for both Criteria and Criteria Range as well :(

Thanks again guys. Will apply this now to my data and let you know.

Cheers,
Ravi.
 
Hi Narayan,

Sorry for delay.

I checked the formula. It doesn't satisfy the requirement. If Criteria 2, 3 & 4 are blank, the formula should do a sumif based on "Criteria 1".

upload_2016-5-20_8-49-26.png

Please check this attached file. Please help me crack this formula :) I am more inclined to find out how this is done. There should be a way to do this. Hope you agree.

Thank you,
Ravi.
 

Attachments

  • Sample (19).xlsx
    12.7 KB · Views: 5
Hi ,

There is a problem of interpretation here.

Suppose Criteria1 has A in it , and the other criteria are all blank. What does this mean ?

Suppose your data in columns J through N is as follows :

A ...... Apple ........................................................ 17

A .........................Market1 .................................. 16

A ................................................... Buy .............. 13

A ......................................................................... 29

Based on this data , what figure should appear in the output ?

Narayan
 
Hi Narayan,

In this case total should be: 75.

Please note that in the data, none of the criteria are blank. It is just the way how the data is being consolidated by all possible combinations possible for further analysis.

It is more like:
A.....Apple.....Market1.....Buy.....17
A.....Apple.....Market1.....Sell.....16
A.....Apple.....Market2.....Buy.....13
A.....Apple.....Market2.....Sell.....29

Further combinations will be:
SumIfs (A)
SumIfs (A, Apple)
SumIfs (A, Apple, Market1)
SumIfs (A, Apple, Market2)
SumIfs (A, Apple, Market1, Buy)
SumIfs (A, Apple, Market1, Sell)
SumIfs (A, Apple, Market2, Buy)
SumIfs (A, Apple, Market2, Sell)


SumIfs (B)
SumIfs (B, Apple)
SumIfs (B, Apple, Market1)
SumIfs (B, Apple, Market1, Buy)

........ goes on

I know I can get these values by using Pivot table, but I need to change fields several times, before I can fill all the totals.

Looking for a quick ONE formula which can get this done in one table.

Hope this gives a better idea.

Thanks again for looking into this.

Cheers,
Ravi.
 
Hi ,

If you confirm that the data cannot have blanks , and that criteria which are blank should not be considered , then try this :

=SUMIFS($N$2:$N$21, $J$2:$J$21, IF(B2 <> "",B2, "*"), $K$2:$K$21, IF(C2 <> "",C2, "*"), $L$2:$L$21, IF(D2 <> "",D2, "*"), $M$2:$M$21, IF(E2 <> "",E2, "*"))

Narayan
 
You must log in or register to reply here.
Back
Top