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

 

[Exhale2020]

Would you post the formula with the reduced range?

It's working fine now, it was an input error.

Thanks for your help Aladin

 

[Exhale2020]

1. Do not refer to whole columns.

2. Do the following better?

=SUMIFS(total,brand,A7,season,IF(B1="","?*",B1),gender,IF(B2="","?*",B2),division,IF(B3="","?*",B3))

=COUNTIFS(brand,A7,season,IF(B1="","?*",B1),gender,IF(B2="","?*",B2),division,IF(B3="","?*",B3))

One last question Aladin, so i can apply the logic in future... What does "?*" mean?

 

[Aladin Akyurek]

One last question Aladin, so i can apply the logic in future... What does "?*" mean?

? and * are so-called wildcards. ? stands for a character; * for zero or more characters.

So, ?* means strings of length 1 or longer. Examples: X, TOPS, 1Q, Y35C...

 

[Exhale2020]

? and * are so-called wildcards. ? stands for a character; * for zero or more characters.

So, ?* means strings of length 1 or longer. Examples: X, TOPS, 1Q, Y35C...

Gotcha! thanks

 

[Aladin Akyurek]

Gotcha! thanks

You are welcome.

 

[Exhale2020]

	A	B	C
1	Season	FW18
2	Gender	MENS
3	Division	Cottons
4
5
6	Brand	Units	CC's
7	Oasis	9	1
8	Gap	0	0
9	ZARA	0	0
10	H&M	0	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In B7 enter and copy down:

=SUMPRODUCT(SUMIFS(total,brand,A7,season,IF(B1="",season,B1),gender,IF(B2="",gender,B2),division,IF(B3="",division,B3)))

In C7 enter and copy down:

=SUMPRODUCT(COUNTIFS(brand,A7,season,IF(B1="",season,B1),gender,IF(B2="",gender,B2),division,IF(B3="",division,B3)))

Hi Aladin,
If I wanted to add another cell to exclude a particular criteria, let's say, something within the 'division' range. How would i add it to the formula?
Thanks!

 

[Aladin Akyurek]

Hi Aladin,
If I wanted to add another cell to exclude a particular criteria, let's say, something within the 'division' range. How would i add it to the formula?
Thanks!

We have now in B3 Cottons. If B3 is left empty, we interpret that as ?*. How/where do you want to indicate an exclusion? Care to provide an exclusion example?

 

[Exhale2020]

We have now in B3 Cottons. If B3 is left empty, we interpret that as ?*. How/where do you want to indicate an exclusion? Care to provide an exclusion example?

Yes correct on the wildcard.

I'd add another cell titled 'Exclude Division' in A4 and the criteria 'cottons' in B4.

Hypothetical:
I can leave all other criteria blank, which would return all results and then enter 'cottons' in B4, to return everything excluding cottons.

 

[Aladin Akyurek]

Yes correct on the wildcard.

I'd add another cell titled 'Exclude Division' in A4 and the criteria 'cottons' in B4.

Hypothetical:
I can leave all other criteria blank, which would return all results and then enter 'cottons' in B4, to return everything excluding cottons.

Book1
	A	B	C	D	E	F	G
1	Season	Gender	Division	Brand	Item Number	Colour	Total
2	FW18	Mens	Bottoms	H&M	xxx123	black	12
3	SS19	WOMENS	TOPS	H&M	XX1234	WHITE	4
4	FW18	MENS	COTTONS	ZARA	XXX22	BLUE	7
5	FW18	MENS	TOPS	OASIS	12342	WHITE	8
6	SS18	MENS	TOPS	ZARA	56WHY2	BLUE	8
7	FW18	MENS	COTTONS	OASIS	44HT53	BLACK	9
8	SS18	WOMENS	TOPS	ZARA	XX1244	BLUE	10
9	FW18	MENS	TEES	OASIS	134GG	ORANGE	12
10	FW18	WOMENS	COTTONS	ZARA	GHR381	BLACK	5
Sheet2

Book1
	A	B	C
1	Season	FW18
2	Gender	MENS
3	Division
4	Exclude Division	Cottons
5
6	Brand	Units	CC's
7	Oasis	20	2
8	Gap	0	0
9	ZARA	0	0
10	H&M	0	0
Sheet1

B7 of Sheet1 must house:

=SUMIFS(total,brand,A7,season,IF(B1="","?*",B1),gender,IF(B2="","?*",B2),division,IF(B3="","?*",B3),division,"<>"&B4)

C7 of Sheet1must house:

=COUNTIFS(brand,A7,season,IF(B1="","?*",B1),gender,IF(B2="","?*",B2),division,IF(B3="","?*",B3),division,"<>"&B4)

 

[Exhale2020]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

	A	B	C
1	Season	FW18
2	Gender	MENS
3	Division
4	Exclude Division	Cottons
5
6	Brand	Units	CC's
7	Oasis	20	2
8	Gap	0	0
9	ZARA	0	0
10	H&M	0	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

B7 of Sheet1 must house:

=SUMIFS(total,brand,A7,season,IF(B1="","?*",B1),gender,IF(B2="","?*",B2),division,IF(B3="","?*",B3),division,"<>"&B4)

C7 of Sheet1must house:

=COUNTIFS(brand,A7,season,IF(B1="","?*",B1),gender,IF(B2="","?*",B2),division,IF(B3="","?*",B3),division,"<>"&B4)

Beautiful thank you!