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. ### JeepcrazNew 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

Thank you
2. ### p45calWell-Known Member

Messages:
1,247
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. ### JeepcrazNew 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. ### NARAYANK991Excel 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. ### JeepcrazNew Member

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

Messages:
905
A sample file could be helpful here. Jeepcraz, can you upload one please? A few representative lines will do.
7. ### NARAYANK991Excel 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. ### JeepcrazNew 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

File size:
144 KB
Views:
8
9. ### NARAYANK991Excel 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. ### JeepcrazNew Member

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

Messages:
16,619
Hi ,

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

Narayan
12. ### Peter BartholomewWell-Known Member

Messages:
583
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. ### JeepcrazNew 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. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

Can you upload the actual file ?

Narayan
15. ### JeepcrazNew Member

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

File size:
340 KB
Views:
6
16. ### p45calWell-Known Member

Messages:
1,247
Is something missing in cell C5?
17. ### NARAYANK991Excel 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. ### NARAYANK991Excel 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. ### JeepcrazNew 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

File size:
340.5 KB
Views:
5
20. ### NARAYANK991Excel 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. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

See if this is OK.

Narayan

#### Attached Files:

• ###### sumif-countif-formula.xls
File size:
530.5 KB
Views:
9
Jeepcraz likes this.
22. ### JeepcrazNew 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. ### NARAYANK991Excel 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. ### JeepcrazNew 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. ### NARAYANK991Excel 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