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

Benders

Board Regular
Joined
Mar 18, 2014
Messages
75
Hi

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

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

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

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
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
Joined
Mar 18, 2014
Messages
75
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
Joined
Aug 13, 2008
Messages
515
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
Joined
Aug 13, 2008
Messages
515
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
Joined
Mar 18, 2014
Messages
75
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
Joined
Aug 13, 2008
Messages
515
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
Joined
Mar 18, 2014
Messages
75
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</SPAN>B</SPAN>C</SPAN>D</SPAN>E</SPAN>F</SPAN>G</SPAN>H</SPAN>I</SPAN>J</SPAN>K</SPAN>
2</SPAN>Fruits</SPAN>Vendors</SPAN>Quality</SPAN>Fairtrade</SPAN>
3</SPAN>Apples</SPAN>TE</SPAN>Low</SPAN>Yes</SPAN>
4</SPAN>Mangoes</SPAN>SA</SPAN>Medium</SPAN>No</SPAN>Item</SPAN>Fruits</SPAN>Vendors</SPAN>Quality</SPAN>Fairtrade</SPAN>
5</SPAN>Grapes</SPAN>WA</SPAN>High</SPAN>Yes</SPAN>Selection</SPAN>Apples</SPAN>
6</SPAN>Apples</SPAN>MO</SPAN>Low</SPAN>No</SPAN>
7</SPAN>Mangoes</SPAN> Medium</SPAN>Yes</SPAN>
8</SPAN>Grapes</SPAN>TE</SPAN>High</SPAN>No</SPAN>
9</SPAN>Apples</SPAN>SA</SPAN>Low</SPAN>Yes</SPAN>
10</SPAN>Mangoes</SPAN>WA</SPAN>No</SPAN>Count</SPAN>
11</SPAN>Grapes</SPAN>MO</SPAN>Yes</SPAN>5</SPAN>
12</SPAN>Apples</SPAN>MO</SPAN>Yes</SPAN>
13</SPAN>Mangoes</SPAN>TE</SPAN>Yes</SPAN>Formula above is given below</SPAN>
14</SPAN>Grapes</SPAN>SA</SPAN>No</SPAN>=DCOUNTA($B$2:$E$17,H4,H4:K5)</SPAN>
15</SPAN>Apples</SPAN>WA</SPAN>Yes</SPAN>
16</SPAN>Mangoes</SPAN>MO</SPAN>No</SPAN>
17</SPAN>Grapes</SPAN>TE</SPAN>High</SPAN>Yes</SPAN>

<TBODY>
</TBODY>
 

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
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.
 

Forum statistics

Threads
1,082,276
Messages
5,364,196
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top