Exhale2020
New Member
- Joined
- Sep 30, 2018
- Messages
- 13
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).
<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:
<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
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