I require a formula to count the number of times a word appears in a range and out of this range I need to count the number of blank spaces in another range with reference only to the first formula.

These are my two formulas that work separately however I need the total number together. =COUNTIF(G2:G473,G476) =COUNTBLANK(D2:D472)

So if the first formual is requal to 50 I want to know how many of those 50 from column G have a blank space in row D.

  • Use COUNTIFS instead of COUNTIF. Make sure the size of the two ranges is equal: in the example you give you have one range ending at row 472 and the other at row 473. Oct 25, 2021 at 5:26
  • Thank you. Still no luck. What do I put between the two formulas so they are both considered? I've tried & and * =COUNTIFS(G2:G470,G474)*COUNTBLANK(D2:D472)
    – C Sando
    Oct 25, 2021 at 10:50
  • You don't use 2 formulas. You use a single formula (COUNTIFS) which references both ranges with an appropriate criteria for each. Oct 25, 2021 at 11:05

2 Answers 2


Table showing Things and One column of Spaces or No Space

In the range A2:A30; I have entered few words. In A33 = CAT, say. Look Up Value. In B2 enter the formula ; A2=$A$33, Just to check whether it is true or false for CAT Copy this formula of B2; down the colomn. Wherever CAT it will show TRUE. In Column D; List of words, given or not given. That means Space or No Space. In Cell C2 enter the formula; =D2=””. If Space TRUE else FALSE will be displayed. Now in Cell B33 enter the following formula ; B33= COUNTIFS($A$2:$A$30,A33,$B$2:$B$30,TRUE,$C$2:$C$30,TRUE) For CAT it displays 9. In place of CAT in A33; you can place DOG, APPLE, LION, etc, to confirm the formula. THANKS.


try this in cell

B33 = COUNTIF($A$2:$A$30,A33)-COUNTIF(D2:D30,A33)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .