# Counting Text using Countifs with Multiple Criteria filters and data contains Blanks/Null

#### Benders

##### Board Regular
Hi

I am unable to count using using Countifs with Multiple Criteria filters and data contains Blanks/Null. Can someone please help?

 Fruits Vendors Quality Fairtrade Apples TE Low Yes Mangoes SA Medium No Item Dropdown Selections Grapes WA High Yes Fruits Mangoes Apples MO Low No Vendors Mangoes Medium Yes Quality Medium Grapes TE High No Fairtrade Yes Apples SA Low Yes Mangoes WA Medium No Count = 0 Grapes MO High Apples Low Mangoes TE Medium Formula in Cell above Grapes SA High No =COUNTIFS(A2:A16,G4,B2:B16,G5,C2:C16,G6,D2:D16,G7) Apples WA Low Yes Mangoes MO Medium No Grapes High Yes

<TBODY>
</TBODY><COLGROUP><COL span=2><COL span=2><COL><COL><COL><COL><COL></COLGROUP>

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### BGY23

##### Well-known Member
Your coutifs formula doesn't like the blank cells. I suggest you put soemthing in Like "-". The formula worked for me then.

Alternatively you insert a coolumn at A and put a 1 in each cell and use sum(if insted of countifs(. Sum(if will work with blank cells however the syntax is different.

Assuming you've put the new column A in ( you can hide it if you don't want to see it later)

=sum(if(B2:B16=H20,If(C2:C16=H21,if(D2:D16=H22,if(E2:E16=H23,A2:A16)))))
 This is an array formula so you must press Ctrl Shift and Enter together when enerting it, not just Enter. Good Luck

<COLGROUP><COL style="WIDTH: 492pt; mso-width-source: userset; mso-width-alt: 23990" width=656><TBODY>
</TBODY>

#### Benders

##### Board Regular
Sorry but if you remove all except one from the Drop Down selections (e.g. just keep Fruits=Mangoes), the formula does not work and I am referring to the Array formula. The idea is the that the user can selection any combination from the Drop down and the Count should change based on the selections. The guy I am making this for hates Pivots and hence trying to use this technique.

#### BGY23

##### Well-known Member
When you say its not working what do you mean ? What is the formula cell saying. I tried it in my spreadsheet and it returned zero because there are not any records that have just Mangoes on the same row. When I made a record with Mangoes and the other colmns blank, it worked ok.

If you get #Value in the formula cell it's because you didn't press Ctrl Shift and Enter together to enter the formula, you must do this.

let me know what you're getting.

#### BGY23

##### Well-known Member

If your expecting the formula to just count the rows with Mangoes irrespective of data in any other column ? It won't. It's checking for the conditions you specified. To do that you need to set up separate formulas that check for the one condition.

#### Benders

##### Board Regular
I actually do want the formula to just count the rows with Mangoes irrespective of data in any other column. The count is based on the Drop Down choices made. If the user chooses only one drop down option and chooses Mangoes then the count of all Mangoes should appear, If now the user chooses the second drop down to select a vendor then the formula should reflect likewise and so on.

#### BGY23

##### Well-known Member

That particular formula won't do that unless all the other columns are blank. You're asking it to test for four conditions and return a value. But what you want to do is say check for these conditions but ignore them when I want you to ? How would it know when and when not to ignore them.

Do 4 more cells with the condition just for Fruit, vendor, Quality or Fairtrade. If you PM me your email address I'll send you my file and you can have a look at that see if it will give you what you want.

The question then of course is what you do for A particula fruit and vendor or Vendor and fairtrade etc.

For that Kind of flexibilty you will need a Pivot table.

#### Benders

##### Board Regular
I think I may have solved this. I used DCOUNTA and had to make a few modifications to the way the data is presented. Apologies for the format in which I am presenting this.

 1 B C D E F G H I J K 2 Fruits Vendors Quality Fairtrade 3 Apples TE Low Yes 4 Mangoes SA Medium No Item Fruits Vendors Quality Fairtrade 5 Grapes WA High Yes Selection Apples 6 Apples MO Low No 7 Mangoes Medium Yes 8 Grapes TE High No 9 Apples SA Low Yes 10 Mangoes WA No Count 11 Grapes MO Yes 5 12 Apples MO Yes 13 Mangoes TE Yes Formula above is given below 14 Grapes SA No =DCOUNTA(\$B\$2:\$E\$17,H4,H4:K5) 15 Apples WA Yes 16 Mangoes MO No 17 Grapes TE High Yes

<TBODY>
</TBODY>

#### BGY23

##### Well-known Member
Hi,

That's great. Well done. They say necessity is the mother of invention. If it works for you all well and good, thats what the forums all about.

Of course now we can all shre in this new (to me) formula. Not seen it before.

thanks.

Replies
3
Views
254
Replies
3
Views
274
Replies
6
Views
2K
Replies
2
Views
182
Replies
1
Views
170