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

[Benders]

Hi

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

[TABLE="width: 781"]
<TBODY>[TR]
[TD]Fruits</SPAN>[/TD]
[TD]Vendors</SPAN>[/TD]
[TD]Quality</SPAN>[/TD]
[TD]Fairtrade</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mangoes</SPAN>[/TD]
[TD]SA</SPAN>[/TD]
[TD]Medium</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD]Item</SPAN>[/TD]
[TD]Dropdown Selections</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes</SPAN>[/TD]
[TD]WA</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD]Fruits</SPAN>[/TD]
[TD]Mangoes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD]Vendors</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mangoes</SPAN>[/TD]
[TD] [/TD]
[TD]Medium</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD]Quality</SPAN>[/TD]
[TD]Medium</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD]Fairtrade</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples</SPAN>[/TD]
[TD]SA</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mangoes</SPAN>[/TD]
[TD]WA</SPAN>[/TD]
[TD]Medium</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD]Count = 0</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples</SPAN>[/TD]
[TD] [/TD]
[TD]Low</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mangoes</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD]Medium</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 2"]Formula in Cell above</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes</SPAN>[/TD]
[TD]SA</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 4"]=COUNTIFS(A2:A16,G4,B2:B16,G5,C2:C16,G6,D2:D16,G7)</SPAN>[/TD]
[/TR]
[TR]
[TD]Apples</SPAN>[/TD]
[TD]WA</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mangoes</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD]Medium</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes</SPAN>[/TD]
[TD] [/TD]
[TD]High</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL span=2><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[BGY23]

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)))))
[TABLE="width: 492"]
<COLGROUP><COL style="WIDTH: 492pt; mso-width-source: userset; mso-width-alt: 23990" width=656><TBODY>[TR]
[TD="class: xl63, width: 656, bgcolor: white"]This is an array formula so you must press Ctrl Shift and Enter together when enerting it, not just Enter.

Good Luck[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Benders]

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]

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]

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]

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]

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]

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.

[TABLE="width: 801"]
<TBODY>[TR]
[TD]1</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[TD]K</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Fruits</SPAN>[/TD]
[TD]Vendors</SPAN>[/TD]
[TD]Quality</SPAN>[/TD]
[TD]Fairtrade</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]Mangoes</SPAN>[/TD]
[TD]SA</SPAN>[/TD]
[TD]Medium</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD]Item</SPAN>[/TD]
[TD]Fruits</SPAN>[/TD]
[TD]Vendors</SPAN>[/TD]
[TD]Quality</SPAN>[/TD]
[TD]Fairtrade</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]Grapes</SPAN>[/TD]
[TD]WA</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD]Selection</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]Mangoes</SPAN>[/TD]
[TD] [/TD]
[TD]Medium</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]Grapes</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD]SA</SPAN>[/TD]
[TD]Low</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]Mangoes</SPAN>[/TD]
[TD]WA</SPAN>[/TD]
[TD][/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD]Count</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]Grapes</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD][/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]5</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD][/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]Mangoes</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD][/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 2"]Formula above is given below</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]Grapes</SPAN>[/TD]
[TD]SA</SPAN>[/TD]
[TD][/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 3"]=DCOUNTA($B$2:$E$17,H4,H4:K5)</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD]WA</SPAN>[/TD]
[TD][/TD]
[TD]Yes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD]Mangoes</SPAN>[/TD]
[TD]MO</SPAN>[/TD]
[TD][/TD]
[TD]No</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]Grapes</SPAN>[/TD]
[TD]TE</SPAN>[/TD]
[TD]High</SPAN>[/TD]
[TD]Yes</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]

 

[BGY23]

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.