Including blank cells in Countifs and Sumifs

[PaulusPigus]

I am using COUNTIFS with multiple criteria which must all be satisfied as follows:
=COUNTIFS(Dates,">="&Date, Sectors,"*"&Sector&"*",Countries,"*"&Country&"*",Regions,"*"&Region&"*",Categories, "*"&Category&"*", Risk_Score, ">="&Risk_Level, Sources, "*"&A14&"*")
Problem is that this does not count a record if any condition cells are blank, for example, if Country or Region is blank
So I am looking to count all records where each of the specified conditions is met or where the condition is not specified:

Specified conditions:

Database fields (subset):

. It seems that Sumproduct may be the answer but I can not work it through.

Linked to this, I want to sum the values in one column (Risk_Score), based on the same criteria. So I currently have:
=SUMIFS(Risk_Score,Dates,">="&Date,Sectors,"*"&Sector&"*",Countries,"*"&Country&"*",Regions, "*"&Region&"*",Categories, "*"&Category&"*", Risk_Score, ">="&Risk_Level, Sources, "*"&A14&"*")

Any help appreciated.

​

 

[awoohaw]

To help people help you, can you use the xl2bb add in and post a mini workbook with a sample of your data and the formulas? People will not have to recreate your workbook this way. And what version of excel are you in?

 

[awoohaw]

Are there any conditions that you want excluded if there is an empty data element? If not count the rows.

 

[PaulusPigus]

Hi, thanks for the quick response. I will try to find time to set up the add-in, but bit pushed right now.

However, in meantime, no conditions to be excluded. I just want to filter rows according to any stated condition, but otherwise to count them. So if no conditions are set, then the whole database should be counted (about 21,000 rows). As I progressively enter conditions, the resulting count would get smaller and smaller. Make sense?

 

[awoohaw]

If you have 365 or 2021 versions, the filter function may work better. But say blanks are okay everywhere you essentially selecting all.

 

[PaulusPigus]

If you have 365 or 2021 versions, the filter function may work better. But say blanks are okay everywhere you essentially selecting all.

Sorry I am not quite with you....

 

[awoohaw]

Okay, yes, you're right, as I think it isn't selecting all. because if column A = {4,5, " "} and you want A = 4, {" "} you will not ge the the 5's. My bad.

What version do you have? FILTER could work. You need to filter an array with an array (your 'target' value, plus blank).
Some folks may be able to post an answer without, but seeing a sample of your data (anonymized if necessary) would be a big help.

 

[PaulusPigus]

Okay, yes, you're right, as I think it isn't selecting all. because if column A = {4,5, " "} and you want A = 4, {" "} you will not ge the the 5's. My bad.

What version do you have? FILTER could work. You need to filter an array with an array (your 'target' value, plus blank).
Some folks may be able to post an answer without, but seeing a sample of your data (anonymized if necessary) would be a big help.

Thanks. I will look at using FILTER in combination with COUNTIF, I guess you mean? Right now I can not use the add-in because the sheet is protected and I can not find my password!

 

[awoohaw]

Thanks. I will look at using FILTER in combination with COUNTIF, I guess you mean? Right now I can not use the add-in because the sheet is protected and I can not find my password!

okay, maybe you can copy the sheet into a new workbook. but Your version of excel is important. Filter is not available to everyone.

 

[PaulusPigus]

I am using Office 365 and I can use Filter. But I am not sure how to get it working with countif.