Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    If statements with two critera

    In the attached worksheet sample, Using the data on the left (A1 - F 92)and using an "IF" STATEMENT. to match the LOCATION # with the TYPE and RECORD the % amount from column D onto the table (K2 - R 12) in the appropriate cell.

    I do not want to use the copy and transpose for the data is voluminous and would not be feasible.

    Any formula suggestions?

    Thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    in K2:
    =SUMPRODUCT(($A$2:$A$92=$J2)*($B$2:$B$92=K$1)*$D$2 :$D$92)

    Copy across the row and down the colum

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I will give this a try but why wouldn't an if statement with the specific criteria of location and type be better and more exact.

    The other reason I prefer this to sumproduct is because I am not sure how the SUMPRODUCT work. Any insights on this?

    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I tried the sumproduct formula from above on the actual workbook. it populated with 0 only. the difference is the data is on a separate worksheet from the result or target worksheet. I do not think that that should be an issue, how ever it recorded a zero 0 under the type on the target worksheet.

    Any ideas??

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What is different about the actual workbook compared to the sample? It works in the sample workbook?

    Concerning an IF statement over the sumproduct, I used the sumproduct since it uses array implicitly without having to explicit indicate to excel (using control-shift-enter) to determine it. You can do it with an IF (confirm with ctrl-shift-enter)
    =SUM(IF(($A$2:$A$92=$J2)*($B$2:$B$92=K$1),$D$2:$D$ 92))

    or even a SUMIFS:
    =SUMIFS($D$2:$D$92, $A$2:$A$92,$J2,$B$2:$B$92,K$1)

    They all should be getting the same number. SUMIFS is probably the most efficient. I used the SUMPRODUCT out of habit, but it is more general as it can be used in excel versions before XL2007.

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Note: A pivot table could be used to create the Location / Type table directly...

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you. sometimes you can't see the forest because of the bushes!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •