Count IF + CountBlank Not working

[paulcherianc]

Hi!

I am not getting the results when I tried to sum Count if result + Countblank. Can anyone advise me please.

=COUNTIF(M7:AQ7,$AP$1)+COUNTBLANK(M7:AQ7)

 

[AliGW]

Maybe this?

=COUNTIF(M7:AQ7,$AP$1)+COUNTIF(M7:AQ7,"")

 

[paulcherianc]

Sorry its not working Ali!

 

[NARAYANK991]

Hi ,

It entirely depends on the data in the range M7:AQ7 , AP1 , and on what according to you the correct result should be.

Probably if you can mention all of the above , it will make it easier to suggest the correct formula.

Narayan

 

[paulcherianc]

Dear Narayan!

I have attached a sample book for better understanding. Can you please look into it.

 

[NARAYANK991]

Hi ,

I am still not fully clear on your requirement , since there is only one sample.

Try this array formula , to be entered using CTRL SHIFT ENTER :

=($A$7 = "PCS") * (COUNTBLANK($M$7:$AQ$7) + SUM(COUNTIF($AP$1:$AT$1, $M$7:$AQ$7)))

Narayan

 

[paulcherianc]

Dear Narayan,

I tried with your formula but it's not working. Sorry for the clarity. I have detailed the info on the attached document. Can you please have a look.

 

[paulcherianc]

Please ignore the above attachment. I have attached the right one.

 

[NARAYANK991]

Hi ,

See your file.

Narayan

 

[paulcherianc]

Perfect Narayan!!! Many Thanks!!!

 

[paulcherianc]

Narayan when I am editing on the range M7:AQ, the results are not automatically updated. How can I fix this?

 

[paulcherianc]

Suppose if I am adding Legend A to any field the formula is not giving the right result.

 

[NARAYANK991]

Hi ,

Please upload your file with the changes you have made to the data.

I am not able to understand the explanation given.

Narayan

 

[paulcherianc]

Really Sorry Narayan, the file is working properly when i checked it again. Actually i for got to press Crtl + Shift + Enter!

 

[NARAYANK991]

Hi ,

Thanks for the feedback.

Narayan

 

[paulcherianc]

Dear Narayan,

Sorry to bother you again. how can I state the "If not" in the same formula?

For Eg: If not then "=AS7"

 

[paulcherianc]

I have updated the formula and its working now.

=IF(A7="PCS",((A7="PCS")*(COUNTBLANK(M7:AQ7)+SUM(COUNTIF($AP$1:$AT$1,M7:AQ7)))),AS7)

 

[NARAYANK991]

Hi ,

Since the IF function is checking for PCS , the formula can be simplified to :

=IF(A7="PCS",COUNTBLANK(M7:AQ7)+SUM(COUNTIF($AP$1:$AT$1,M7:AQ7)),AS7)

entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[John Jairo V]

Hi, to all!

Another way (CSE too):
=IF(A7="PCS",SUM(N(M7:AQ7=TRANSPOSE(AO$1:AT$1))),AS7)

Or (without CSE):
=IF(A7="PCS",SUMPRODUCT(COUNTIF(M7:AQ7,AO$1:AT$1&"")),AS7)

Blessings!