Count problem / logic

stefan8871

stefan8871

New member
Joined
Oct 26, 2016
Messages
9
Reaction score
0
Points
0
Hello excel gurus.

I am trying to count the blank fields and a certain text from a column. So far I've come with =COUNTBLANK(H2:H10)+COUNTIFS(H2:H70;"CNT"), but the problem is that I need to update every formula with the countblank range. I need a formula that counts the blank fields and a certain text (CNT) only if that row has some data, for example if it has YES or NO on the B column.

Also field 8 will have other text as well, I want that text to be ignored in the counting formula.

Please, can I get some help.

Much appreciated!

View attachment excel document.xlsx
 
Please provide a worksheet containing manual expected outcomes. It is not clear from your current attachment what it is you are asking us to solve.
 
In the current worksheet, I want a formula that counts the blank fields + "CNT" value for the H column rows 2 to 10. But my main problem is how to create the formula in order for the count to check first if the rows 2 to 10 have on column B the value YES or NO.

I don't want to update each range formula with the exact rows to count because the table I'm using is constantly updated so this would mean I need to update ~80 formulas every week.

I need a total of "blank fields" + "CNT" values from column H.

I hope this clears the fog :)

Thank you!
 
stefan8871 said:
In the current worksheet, I want a formula that counts the blank fields + "CNT" value for the H column rows 2 to 10. But my main problem is how to create the formula in order for the count to check first if the rows 2 to 10 have on column B the value YES or NO.
Click to expand...
Perhaps
Code: 
=COUNTBLANK(H2:H10)+COUNTIFS(H2:H10;"CNT";B2:B10;"Yes")
 

Attachments

  • stefan8871-navic.xlsx
    12.9 KB · Views: 2
Last edited:
The formula does not return the full result.

I also need the results from the rows with No on B column.

Counting the blank and CNT fields would result in value 7. (blank fields on rows 2,7,8,9 + CNT on rows 3,4,10)
 
Again, please make it easier for those trying to help by providing a workbook that contains expected outcomes. Help us to help you.
 
stefan8871 said:
I also need the results from the rows with No on B column.
Counting the blank and CNT fields would result in value 7. (blank fields on rows 2,7,8,9 + CNT on rows 3,4,10)
Click to expand...
Perhaps
Code: 
=COUNTBLANK(H2:H10)+COUNTIF(H2:H70;"CNT")+COUNTIF(B2:B10;"NO")
 
I am not quite sure I understand what you require when you ask for a workbook, I've already attached the document I'm working on, I am still looking for a better solution. Currently none of the formulas from above count the TOTAL number of BLANK cells + number of CNT cells from column H. So far I've come up with the following formula =COUNTBLANK(H2:H10)+COUNTIFS(H2:H70;"CNT") which gives the result needed (current case is 7).

What I'm looking for is a formula which returns the same result but the range where it looks for values should be based on column B where the values are either YES and NO.

View attachment excel document (2).xlsx
 
How about this?

=SUMPRODUCT(($H$2:$H$10="CNT")*($B$2:$B$10="Yes"))
 
This formula only counts the CNT values for the rows that have the YES value on column B. I need the total number of CNT values + total number of blank cells from column H where column B has yes and no. Does this make any sense?
 
Like this?

=COUNTIF($H$2:$H$10,"CNT")+SUMPRODUCT(($H$2:$H$10="")*($B$2:$B$10="Yes"))
 
Or maybe even this?

=COUNTIF($H$2:$H$10,"CNT")+SUMPRODUCT(($H$2:$H$10="")*($B$2:$B$10="Yes"))+SUMPRODUCT(($H$2:$H$10="")*($B$2:$B$10="No"))
 
I think I'm almost there. So the formula is not complete. It only needs to count the blank and CNT cells from the Rows where value "No" is present. It currently counts only the CNT values where the value "No" is present.
 
Yes, this does it!

=COUNTIF($H$2:$H$10,"CNT")+SUMPRODUCT(($H$2:$H$10="")*($B$2:$B$10="Yes"))+SUMPRODUCT(($H$2:$H$10="")*($B$2:$B$10="No"))​



Thank you very much!
 
That seems to be different to what you said previously, but how about this?

=SUMPRODUCT(($H$2:$H$10="")*($B$2:$B$10="No"))+SUMPRODUCT(($H$2:$H$10="CNT")*($B$2:$B$10="No"))
 
Glad we have it sorted! It would have helped if you had broken your requirement down and told us your expected outcomes for each bit of it. :)
 
Could I please ask for some more help?

I'm trying to get this result on a different sheet. I've updated the document to reflect the change.

So basically what I'm trying to do is, have the result displayed on a different sheet, the summary sheet. How should I modify the formula to work?

View attachment excel document (3).xlsx
 
Function solved :)

=COUNTIF(free!H2:H80;"CNT")+SUMPRODUCT((free!H2:H80="")*(free!B2:B80="Yes"))+SUMPRODUCT((free!H2:H80="")*(free!B2:B80="No"))
 
You must log in or register to reply here.
Back
Top