1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Sumproduct

Discussion in 'Ask an Excel Question' started by Jeepcraz, Feb 23, 2018.

  1. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    I need to get excel to recognize two different values in two different column but unable to do so. So i was "forced to" put the value in one single column and used the formula below

    formula used
    =SUMPRODUCT(((October!$C$3:$CQ$3=$G$1)+(October!$C$3:$CQ$3=$H$1))*(((October!$B$7:$B$69)=$A4)*((October!$C$7:$CQ$69="D 12hrs"))))

    To get the formula to count, i have to input my data under Column F
    Column F Column G
    D 12hrs

    I tried to add in more criteria to the formula but it generate a zero value
    Anyone can advise?



    Thank you
  2. p45cal

    p45cal Well-Known Member

    Messages:
    1,237
    try:
    Code (vb):
    =SUMPRODUCT(((October!$C$3:$CQ$3=$G$1)+(October!$C$3:$CQ$3=$H$1))*((October!$B$7:$B$69)=$A4)*(October!$C$7:$CQ$69="D 12hrs"))
    Thomas Kuriakose likes this.
  3. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Hi this is the formula I’m using which is not what I want. I want the formula to able to count if column F is D and column G is 12 hrs
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Try this :

    =SUMPRODUCT(((October!$C$3:$CQ$3=$G$1) + (October!$C$3:$CQ$3=$H$1)) * (October!$B$7:$B$69=$A4) * (October!$F$7:$F$69="D") * (October!$G$7:$G$69 = "12hrs"))

    Narayan
  5. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Yes. Tried thus before but the result is a zero. Was wondering what the problem
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    798
    A sample file could be helpful here. Jeepcraz, can you upload one please? A few representative lines will do.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    May be leading or trailing spaces ; your initial value was :

    D 12hrs

    with a space between the D and the 1.

    Is the D now with a trailing space or the 12hrs with a leading space character ?

    Narayan
  8. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    The cells highlighted in green are the one which i want to count

    I4 contains the formula for weekday

    =SUMPRODUCT(((October!$C$3:$N$3=$B$1)+(October!$C$3:$N$3=$C$1)+(October!$C$3:$N$3=$D$1)+(October!$C$3:$N$3=$E$1)+(October!$C$3:$N$3=$F$1))*(((October!$B$7:$B$69)=$A4)*((October!$C$7:$N$69="D 12hrs"))))

    As you can see, it generates a correct result which is one

    J4 contains the formula for weekend

    =SUMPRODUCT(((October!$C$3:$N$3=$B$1)+(October!$C$3:$N$3=$C$1)+(October!$C$3:$N$3=$D$1)+(October!$C$3:$N$3=$E$1)+(October!$C$3:$N$3=$F$1))*(((October!$B$7:$B$69)=$A4)*(October!$C$7:$N$69="D")*(October!$C$7:$N$69="12hrs")))

    It generates a zero value instead.
    I need the formula to count when both D and 12hrs appeared on two adjacent columns

    Attached Files:

  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Try this array formula , to be entered using CTRL SHIFT ENTER :

    =SUMPRODUCT(MMULT(--((October!$B$7:$B$69 = $A4) * (October!$C$7:$N$69 = "D")) ,TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$N$3 , $G$1:$H$1 , 0) , 0) > 0))) * MMULT(--((October!$B$7:$B$69 = $A4) * (October!$C$7:$N$69 = "12hrs")) , TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$N$3 , $G$1:$H$1 , 0) , 0) > 0))))

    Narayan
  10. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    I have tried this but it gave me a result of 2 instead of 1.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    I get a result of 1 when I try the formula in your latest uploaded file.

    Narayan
  12. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    428
    One minor suggestion. Rather than using

    = (day.ofWeek=Monday)+(day.ofWeek=Tuesday)+(day.ofWeek=Wednesday)+(day.ofWeek=Thursday)+(day.ofWeek=Friday)

    to determine whether the specific day that was worked is a weekday or at the weekend,

    =COUNTIF(weekday.list, day.ofWeek)

    will return 1 for a weekday and 0 for a weekend.

    Notes:
    day.ofWeek refers to: =October!C3:N3
    weekday.list refers to: =B1:F1
    The individual cells of weekday.list are also named but this use of names would be a last resort to avoid direct cell referencing.
    NARAYANK991 likes this.
  13. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Yes I got the same result in the sample file. But when I put it in the actual file, it give me a result of 2
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Can you upload the actual file ?

    Narayan
  15. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Here you go. i have tried to modify the formula but it's giving wrong result

    Attached Files:

  16. p45cal

    p45cal Well-Known Member

    Messages:
    1,237
    Is something missing in cell C5?
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    I can understand the reason for the problem , but as yet I am not able to figure a way out.

    The reason is that there are two cells which have D in them viz. X68 and CI 68 , but only one of them is to be counted because only Y68 has 12hrs in it , while CJ68 is blank. Multiplying 2 by 1 returns 2.

    Narayan
  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See if either of these array formulae work :

    =SUMPRODUCT(IF(MMULT(--((October!$B$7:$B$69=$A4)*(October!$C$7:$CQ$69="D")),TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$CQ$3,$G$1:$H$1,0),0)>0))), MMULT(--((October!$B$7:$B$69=$A4)*(October!$C$7:$CQ$69="12hrs")),TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$CQ$3,$G$1:$H$1,0),0)>0)))))

    =MIN(SUMPRODUCT(MMULT(--((October!$B$7:$B$69=$A4)*(October!$C$7:$CQ$69="D")),TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$CQ$3,$G$1:$H$1,0),0)>0)))), SUMPRODUCT(MMULT(--((October!$B$7:$B$69=$A4)*(October!$C$7:$CQ$69="12hrs")),TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$CQ$3,$G$1:$H$1,0),0)>0)))))

    Narayan
    Jeepcraz likes this.
  19. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Narayan
    Yes it worked for the two formulas. But now there's a problem which i have forgotten to mention. Besides D 12hrs, I have N 12hrs too(the cell in purple). Using the two formulas you gave, it also counts D68 and E68 as one count which I don't want it to happen.

    The purpose of this is to calculate the shift allowance. So Staff on D 12hrs will entitled for Afternoon shift allowance and N 12hrs will entitled for night shift allowance.

    You can see in my cell,
    D is Day shift
    A is Afternoon shift
    N is Night Shift

    D 12hrs = A, N 12hrs= N. So I don't need the formula to calculate N 12hrs since i can use N for it. Hope you understand what i mean. sorry for the confusion

    Attached Files:

  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    So basically , we need to look for only those values where a D in one column is followed by 12hrs in the immediate next column.

    Give me some time to look into it.

    Narayan
  21. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Jeepcraz likes this.
  22. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Yes it works great. Can I know how the formula works?

    =MMULT(INDEX(October!$DC$7:$GQ$69,MATCH($A$4,October!$B$7:$B$69,0),),TRANSPOSE(--(IFERROR(MATCH(October!$C$3:$CQ$3,$B$1:$F$1,0),0)>0)))
  23. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    The range DC7:GQ69 is a set of helper columns which looks for D in one cell and 12hrs in the next adjacent cell in the original range of C7:CQ69 ; so if there is a D in cell AJ33 , if there is no text 12hrs in cell AK33 , the D will not be counted , but if there is , it will be.

    Narayan
    Thomas Kuriakose likes this.
  24. Jeepcraz

    Jeepcraz New Member

    Messages:
    20
    Pardon me, but I still don’t understand why it’s DC7:GQ69. How you derive the set of helper column and how they recognize D and 12hrs from it? I don’t see D and 12hrs inside the formula
  25. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    The formula in DC7 is this :

    =IF(C7="D", IF(D7 = "12HRS", 1, 0), 0)

    And this is copied across , till GQ7 ; this is also copied down till row 69.

    You will see that cells DF68 and DI68 have 1s in them , corresponding to the D in cell F68 and 12hrs in G68 , and D in I68 and 12hrs in J68.

    Narayan

Share This Page