Ignoring multiple blank criteria using countifs / sumifs

[Exhale2020]

Hi all,

I've trawled various forums looking for a solution to this problem without much luck.

My challenge is i'm trying to count a large dynamic range of cells using multiple criteria (4 criteria).

However, what I want to occur is when one (or any/all) of the criteria is blank, I want the formula to ignore that particular criteria (not search for 'blank' as it does now).

Season	FW18
Gender	MENS
Division	Cottons
Brand	Units	CC's
Oasis	sumifs	countifs
Gap	sumifs	countifs
ZARA	sumifs	countifs
H&M	sumifs	countifs

<tbody>
</tbody>

In the above table, 'Brand', 'Season', 'Gender', 'Division'. Are all Criteria. If any or all of the criteria are blank, I want it to be ignored.
The brand list will be dynamic and changes regularly. The Season, Gender & Division will be treated as drop down menus which are dynamic.

The purpose is to return a count in the CC's column.


The table being searched looks like this:

Season	Gender	Division	Brand	Item Number	Colour	Total
FW18	Mens	Bottoms	H&M	xxx123	black	12
SS19	WOMENS	TOPS	H&M	XX1234	WHITE	4
FW18	MENS	COTTONS	ZARA	XXX22	BLUE	7
FW18	MENS	TOPS	OASIS	12342	WHITE	8
SS18	MENS	TOPS	ZARA	56WHY2	BLUE	8
FW18	MENS	COTTONS	OASIS	44HT53	BLACK	9
SS18	WOMENS	TOPS	ZARA	XX1244	BLUE	10
FW18	MENS	TEES	OASIS	134GG	ORANGE	12
FW18	WOMENS	COTTONS	ZARA	GHR381	BLACK	5

<tbody>
</tbody>

The field to count is 'Total'


I've tried a combination of Ifs and CountIFS to ignore when blank, but its extremely lengthy and most likely inefficient. The source data is up to 20,000 rows


I'd also like to do the same with a Sumifs formula.


All help is much appreciated!

Thanks
G

Version: Office 2013
PC

 

[Aladin Akyurek]

Beautiful thank you!

You are welcome.