Log In
Register
Home
September 27, 2018
Offline
Hi dears,
Please see my attached screenshot of Excel sheet
I want the correct formula doing like this:
=COUNTIFS(C2:F33; "=IT448"; D2:E33; <>"")
Thanks
VIP
Trusted Members
June 25, 2016
Offline
Hi Abbas
There is no attachment
September 27, 2018
Offline
Sorry,
Here is the attachment
VIP
Trusted Members
June 25, 2016
Offline
Hi Abbas
What are you trying to achieve?
It looks like you are trying to do 2 things in one formula.
If you want to count cells equal to IT448 then use COUNTIFS(D3:D33,"IT448")
If you wanted to count non-blank cells, then use COUNTA(D3:D33)
It would be better if you attach a worksheet with sample data instead of an image.
Sunny
September 27, 2018
Offline
Hi dear Sunny
find the attached file
Abbas
VIP
Trusted Members
June 25, 2016
Offline
Hi Abbas
What is your expected result in C38 (periods achieved) and C39 (percentage of achievement)?
Are you trying to do something like this?
=COUNTIFS(C3:C34,"IT448",D3:D34,"<>"&"")
Without knowing how you calculate them, it is difficult to give you an exact answer
Sunny
September 27, 2018
Offline
Yes Sunny,
I want to calculate:
How many times "IT448" is mentioned in column C and there is no corresponding number in column D
for example, if (C3=IT448 AND D3=empty) then result=0 else result+=1
if (C27=IT448 AND D27=empty) then result=0 else result+=1
I hope this simplify what I need
VIP
Trusted Members
June 25, 2016
Offline
Hi Abbas
I am confused.
You wanted to count the number of entries where column C contains IT448 and column D must be empty but you expect the result to be 0 instead of 1.
Anyway you can try one of the below formulas:
1) =COUNTIFS(C3:C34,"IT448",D3:D34,"<>"&"") - column C contains IT448 and column D is no empty
2) =COUNTIFS(C3:C34,"IT448",D3:D34,"") - column C contains IT448 and column D is empty
Hope this helps
Sunny
1 Guest(s)
