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

Sumproduct

Jeepcraz

New Member
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
 
try:
Code:
=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"))
 
try:
Code:
=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"))

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

Yes. Tried thus before but the result is a zero. Was wondering what the problem
 
Yes. Tried thus before but the result is a zero. Was wondering what the problem
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
 
A sample file could be helpful here. Jeepcraz, can you upload one please? A few representative lines will do.

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
 

Attachments

  • Shift AllowanceV3.xls
    144 KB · Views: 8
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
 
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

I have tried this but it gave me a result of 2 instead of 1.
 
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.
 
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
 
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
 
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
 

Attachments

  • Shift AllowanceV3 (1).xls
    340.5 KB · Views: 5
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
 
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)))
 
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
 
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
 
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
 
Back
Top