Dear to all,
I’m a new member of the form and I’m positively surprised about the effort people put in solving problems and helping each other. In the past week I improved my Excels skill very rapidly, thanks to the internet and this form. I’m using Excel for about 10 years, although some years more than others Excel knowledge level is somewhere between starter and mid-level. I’m able to make some basic VBA coding and can understand the more complicated VBA codes.
I red multiple topics about counting conditional formatted cells and values. I understood and experienced this approach is complicated. Therefor I tried the same condition as the conditional formatting in a SUMIFS. However I did not got is working. The condition is that the date of that column should be between the start and end date of a column
The formula should sum the values of the column CN15:CN650 when the value of that row complies to the condition: CN11 is between T15:T650 and U15:U650.
The result of the formulas below should be 3 (CM15 + CM17)
The first attempt: (Cell CM5)
1. {=SUMIFS(CM15:CM29;T15:T29;CM$11>=T15:T29;U15:U29;CM$11<=U15:U29)}
Gives a fault and zero, because it does nog recognize the condition.
The second attempt: (Cell CN5)
2. {=SUMIF(CM15:CM29;$T15:$T650<=CM11<=$U15:$U29;CM15:CM29)}
Gives a fault and zero, because it does nog recognize the condition.
The third attempt (Cell CO5)
3. {=IF(EN(CM$11<=$U15:$U29;CM$11>=$T15:$T29);SOM(CM15:CM29);0)}
If the conditions of a single row is satisfied, it sums all values of the column instead of the rows where the conditions is satisfied. .
Who will help me out?
I’m a new member of the form and I’m positively surprised about the effort people put in solving problems and helping each other. In the past week I improved my Excels skill very rapidly, thanks to the internet and this form. I’m using Excel for about 10 years, although some years more than others Excel knowledge level is somewhere between starter and mid-level. I’m able to make some basic VBA coding and can understand the more complicated VBA codes.
Background
Despite the new knowledge I did not manage to solve a problem in my planning sheet. The planning is based on data from our ERP system based on MSQL database. It generates a list with orders a delivery date and the workload for each discipline of a specific order. Based on the delivery date and the amount of work Excel generates starting and ending dates of the different disciplines per order (drawing, editing, assembly). Columns present the week days, rows the different orders. I used conditional formatting for the generation of coloured cells in my planning. Each coloured cell is one man day of work. The following problem should be solved:
To determine the workload, I need to count the values of the coloured cells for each day (column).
Despite the new knowledge I did not manage to solve a problem in my planning sheet. The planning is based on data from our ERP system based on MSQL database. It generates a list with orders a delivery date and the workload for each discipline of a specific order. Based on the delivery date and the amount of work Excel generates starting and ending dates of the different disciplines per order (drawing, editing, assembly). Columns present the week days, rows the different orders. I used conditional formatting for the generation of coloured cells in my planning. Each coloured cell is one man day of work. The following problem should be solved:
To determine the workload, I need to count the values of the coloured cells for each day (column).
I red multiple topics about counting conditional formatted cells and values. I understood and experienced this approach is complicated. Therefor I tried the same condition as the conditional formatting in a SUMIFS. However I did not got is working. The condition is that the date of that column should be between the start and end date of a column
The formula should sum the values of the column CN15:CN650 when the value of that row complies to the condition: CN11 is between T15:T650 and U15:U650.
The result of the formulas below should be 3 (CM15 + CM17)
The first attempt: (Cell CM5)
1. {=SUMIFS(CM15:CM29;T15:T29;CM$11>=T15:T29;U15:U29;CM$11<=U15:U29)}
Gives a fault and zero, because it does nog recognize the condition.
The second attempt: (Cell CN5)
2. {=SUMIF(CM15:CM29;$T15:$T650<=CM11<=$U15:$U29;CM15:CM29)}
Gives a fault and zero, because it does nog recognize the condition.
The third attempt (Cell CO5)
3. {=IF(EN(CM$11<=$U15:$U29;CM$11>=$T15:$T29);SOM(CM15:CM29);0)}
If the conditions of a single row is satisfied, it sums all values of the column instead of the rows where the conditions is satisfied. .
Who will help me out?
