# Excel formula with a multiple formula

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) 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

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)
``````