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]

It's better to post the expected results instead of suggesting/forwarding the type of formulas the would-be helpers must devise.

What are CC's and Units, that is, which values must be addressed in the data (Season, Gender, Division, Brand, Item Number, Colour, or Total)?

 

[Exhale2020]

It's better to post the expected results instead of suggesting/forwarding the type of formulas the would-be helpers must devise.

What are CC's and Units, that is, which values must be addressed in the data (Season, Gender, Division, Brand, Item Number, Colour, or Total)?

Thanks for the reply aladin. I'd upload the actual table but it's not letting me.

Let me try to articulate..

The purpose of the table is to summarize the instances where any of the criteria occurs. Let's focus on just counting as opposed to SUM.

CC's = count of the number of times the combination of any of the criteria occurs in the source data (referencing Total highlighted red).
In this case Season, Gender, Division, Brand (highlighted green). A simple countIFS formula would work, but fails when any of the criteria is blank.

For example, when I leave 'Season' blank, I'd like to return results only for the other criteria.

Hope this is clearer.

 

[Aladin Akyurek]

Sorry. What I'm trying to convey is that when you seek help, you should not suggest/forward a type of formula as solution.
Rather: describe the problem and let the helper decide on the Excel formula.

What do you want to sum from the table under Units and what do you want to count from the table under CC's? Please do not repeat the issue of blanks as it's clear what that means.

 

[Exhale2020]

Sorry. What I'm trying to convey is that when you seek help, you should not suggest/forward a type of formula as solution.
Rather: describe the problem and let the helper decide on the Excel formula.

What do you want to sum from the table under Units and what do you want to count from the table under CC's? Please do not repeat the issue of blanks as it's clear what that means.

For Units = I'm trying to sum the 'Total' column in red
For Count = I'm trying count the 'Total' column in red

 

[Aladin Akyurek]

Book1
	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
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)))

 

[Exhale2020]

Thanks Aladin, the formula works.

However, it's extremely resource heavy and pretty much times out when copied over a few cells.

Do you have any alternative ideas?

Thanks again!

 

[Aladin Akyurek]

Thanks Aladin, the formula works.

However, it's extremely resource heavy and pretty much times out when copied over a few cells.

Do you have any alternative ideas?

Thanks again!

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))

 

[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))

Aladin.. you're a star! Massively quicker.


I've also tried reducing the range to a limited selection but I'm getting VALUE! error.

 

[Aladin Akyurek]

Aladin.. you're a star! Massively quicker.


I've also tried reducing the range to a limited selection but I'm getting VALUE! error.

Would you post the formula with the reduced range?