Solved: help with below formula.

[VISHAL120]

Hi All,

I needed help with the below formula which is not working :

H4-IF(C4<>"",B4,IF(AND(C4<>"",D4<>""),B4*2,IF(AND(C4<>"",D4<>"",E4<>""),B4*3,IF(AND(C4<>"",D4<>"",E4<>"",F4<>""),B4*4,IF(AND(C4<>"",D4<>"",E4<>"",F4<>"",G4<>""),B4*5)))))

In fact i have one column which keeps the daily target example 200 pcs. what i want is to see the varaince from the daily production total versus the targeted qty on a daily basis.

that is every time the daily production is enter the target qty shall change accrodingly.

example :

target : 200 pcs

1st day prod 120,
the target will be : 200
2nd day prod : 120
the target shall be : 400

and then on the varaince column i do total prod - total targeted like :

240-400 to see the varaince .

but with my formula its remaining still with the first day.

thanks to see attached sheet for examples.

Many thanks for the help if this is possible.

:help

[mancubus]

hi.
try
=H4-(B4*COUNTIF(C4:G4,">0"))

[VISHAL120]

Hi,

many thanks for the help its working this way but in real the data is not organize in this way. My error for not posting the correct way the data is organise in fact :

the Day 1 header has 2 column : 1 is OPr and other production and its the production that needs to be counted .

Am trying this but not working :
=(B5*COUNTIF(D5,F5,H5,J5,L5),">0"))


Am attaching a corrected example of how its really organise .

many thanks again for the help.

[mancubus]

try

=(B5*SUM(D5>0,F5>0,H5>0,J5>0,L5>0))

or
=(B5*SUM(COUNTIF(INDIRECT({"D5","F5","H5","J5","L5"}),">0")))

or
=(B5*(COUNTIF(D5,">0")+COUNTIF(F5,">0")+COUNTIF(H5,">0")+COUNTIF(J5,">0")+COUNTIF(L5,">0")))

[VISHAL120]

Hi ,

Thanks a lot its working.