• 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.

Find condition and consolidate

wintermute

New Member
Hi guys,


could you help me with one formula which can fulfill gray area on a picture below:

https://docs.google.com/document/d/1sPgiI1RNXcv7TEnkzD_We-IsVZRHta-FWPqwXAs6kOA/edit?pli=1

I have to consolidate data with layout as picture shows, in orange area.

No problem to sumif with manually changed range for criteria, but I'm looking for solution that can fill whole gray area with just one function.


Thanks in advance
 
Assuming that your data is present in G1:M7 and that your required result is located in A1:D10 following formula works:


=SUMPRODUCT(($G$2:$G$7=$A3)*($K$1:$M$1=B$1)*$K$2:$M$7)+SUMPRODUCT(($H$2:$H$7=$A3)*($K$1:$M$1=B$1)*$K$2:$M$7)+SUMPRODUCT(($I$2:$I$7=$A3)*($K$1:$M$1=B$1)*$K$2:$M$7)+SUMPRODUCT(($J$2:$J$7=$A3)*($K$1:$M$1=B$1)*$K$2:$M$7)


I can also mail you the sheet if required. :p
 
Thanks for answer Faseeh.

But I think that your huge formula is not a best solution.

I thought about one neat nested SUMIF formula with range for criteria changing based on part type.

Something like: =SUMIF(looking for column based on part type;$A2;J:J)

Let's assume there is one column before current A:A with specified part type for each row.

Hope you understand.
 
Hi ,


Would this do ?


=SUMIF(IF(LEFT($A2,1)="F",$F$2:$F$7,IF(LEFT($A2,1)="M",$G$2:$G$7,IF(LEFT($A2,1)="R",$H$2:$H$7,$I$2:$I$7))),$A2,J$2:J$7)


Narayan
 
@wintermute


You are right..formula is a tedious one... any way thanks for your feedback this gives me opportunity to learn.


@NARAYANK991


How do you come up with such a classical solution to problem??.:) This formula is really good!! :)
 
I was writing all sorts of VBA code to do some sorting/analysis on data...then Narayan pops in with a simple SUMPRODUCT formula that worked even better. Who knew these simple formulas had so much mileage?
 
Back
Top