COUNTIFS Question

[bennyx_o]

Hi Guys,

I'm having an issue with COUNTIFS - I currently have a spreadsheet with data (increases on daily basis, ended up at 50k rows last year) which I'm trying to create a simple report on a separate sheet from. The report has 3 selectable variables - week number, Agent & Product. If all 3 are selected, the formula work fine.

Here's my problem - if one of the 3 selectable variables is blank, I want it to ignore that criteria. For example, the agent field is left empty, I want it to return results based on all agents.

Is this possible with COUNTIFS? I was considering using ISBLANK and creating a formula for each possible combination, but I'm hoping there's an easier way. I also want to stay clear of pivot tables.

Anyone any ideas? I've nevr used SUMPRODUCT, but could this work instead?

Thanks!

 

[bennyx_o]

I forgot to include this in my original post. The master data is recorded as below (With 2 columns excluded)

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD]Month[/TD]
[TD]Product[/TD]
[TD]Call Attempt[/TD]
[TD]Outcome[/TD]
[TD]Source[/TD]
[TD]Agent[/TD]
[/TR]
[TR]
[TD]02/01/2018[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]Product 1[/TD]
[TD]Call Attempt 1[/TD]
[TD]Outcome 1[/TD]
[TD]Source 1[/TD]
[TD]Agent 1[/TD]
[/TR]
[TR]
[TD]02/01/2018[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]Product 2[/TD]
[TD]Inbound[/TD]
[TD]Outcome 2[/TD]
[TD]Source 2[/TD]
[TD]Agent 2[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Product 3[/TD]
[TD]Call Attempt 2[/TD]
[TD]Outcome 1[/TD]
[TD]Source 1[/TD]
[TD]Agent 3[/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Product 4[/TD]
[TD]Call Attempt 3[/TD]
[TD]Outcome 3[/TD]
[TD]Source 1[/TD]
[TD]Agent 4[/TD]
[/TR]
</tbody>[/TABLE]

And below is a screenshot of what I'm trying to populate;

Week is a freehand entry and both Agent & Product are dropdown boxes. As above, is it possible to exclude, for example, week number if the cell is empty and just return a count based on Agent & Product?

Thanks again.

 

[Aladin Akyurek]

Try...

=COUNTIFS(WeekRange,IF(Week="","<>",Week),AgentRange,Agent,ProductRange,Product)