Sum with Multiple Condition & also with multiple value as Condition in a Column

[Nishant859_1]

Hi
I am Nishant
Want to Calculate Resolution time of the problem, based on Criteria like Months, Category 1, category 2
I can use simple SUMIFS for this
but there are multiple type of Category 1 & Category 2
and that will change according to requirement
so I make 2 column & put all non required category 1 & 2 for calculation in a column

For example
Category 1 :- aa,bb,cc,dd,....zz
Category 2 :- 111,222,333,555...

I don't want to calculate aa,cc,dd from Category 1
& don't want to calculate 222,666,777 Category 2 :-

 

[xlstime]

Can you please upload same sheet with required output

Hi
I am Nishant
Want to Calculate Resolution time of the problem, based on Criteria like Months, Category 1, category 2
I can use simple SUMIFS for this
but there are multiple type of Category 1 & Category 2
and that will change according to requirement
so I make 2 column & put all non required category 1 & 2 for calculation in a column

For example
Category 1 :- aa,bb,cc,dd,....zz
Category 2 :- 111,222,333,555...

I don't want to calculate aa,cc,dd from Category 1
& don't want to calculate 222,666,777 Category 2 :-

 

[Nishant859_1]

EXCEL FOR YOUR REFERENCE
I DID THIS BY USING IF
BUT WANT TO KNOW ANOTHER WAY

 

[Nebu]

Hi:

I did not understand completely what you are looking for here as your explanation is not very clear. From what I understood, do a pivot table do what you are looking for?

Thanks

 

[Nishant859_1]

Same thing I am looking for but without pivot table
I want to use formula

 

[Nebu]

Hi:

Use the following formula.

=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$1774,$K$4:$K$5,0))),--($D$2:$D$1774=I11),--(ISERROR(MATCH($C$2:$C$1774,$I$4:$I$5,0))),--(ISERROR(MATCH($B$2:$B$1774,$J$4:$J$7,0))),$E$2:$E$1774)

Thanks

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[xlstime]

Awesome NARAYANK, very good use of TRANSPOSE

Hi ,

See the attached file.

Narayan

 

[Nishant859_1]

Thanks for helping me