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]

well, it may not work with it. we'll have to figure it out. You question is tough to me. so it will take me some time. but others may chime in if they want.

 

[PaulusPigus]

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

Just wondering, is there a 'cheat' here - could I place some content in the 'blank' condition s=cells which would ensure that they are counted?

 

[PaulusPigus]

well, it may not work with it. we'll have to figure it out. You question is tough to me. so it will take me some time. but others may chime in if they want.

ok thanks for your thoughts anyway

 

[awoohaw]

If you could post some sample data as you have it in excel it would be great. The xl2bb add in is what you need for that.

 

[PaulusPigus]

Sorted it using approach here SUMIFS Does Not Work With Blank Cells