Counting Text based on TWO Criteria

[CraftedMcNulty]

Hi,

I'm looking to count Unique Accounts based on two criteria: Supplier and Month

Accounts are listed in a data tab in column B, the suppliers are listed in the data tab in column K, and the month is listed in the data tab in column G.

Here is the arrangement of the "dashboard" I'm looking to have this feed into - assume "Suppliers" is in the "Dashboard Tab" in cell A1. I've played around with COUNTIFS but I'm not getting what I need... Any help is greatly appreciated! Essentially this would return how many unique accounts purchased each supplier for each month. I can pivot this quite easily but rolling forward the data requires rebuilding the pivot each time and I'd like to just solve it through a formula:

[TABLE="width: 1167"]
<colgroup><col><col span="3"><col><col span="5"><col span="3"><col></colgroup><tbody>[TR]
[TD]Suppliers[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Supplier 1[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Supplier 2[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Supplier 3[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Supplier 4[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Supplier 5[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

you say unique accounts - i assume a supplier can purchase the same account in the same month multiple times

something like
countifs( DataTab!$K:$K , $A2, DataTab!$G:$G, ">="&B$1,DataTab!$G:$G,"<="&EOMONTH(B$1,0))


will count the accounts by supplier by month - assuming the date in the dashboad is set to 1/1/17 1/2/17 etc UK date format
so change to US if required
AND the DataTab sheet has dates in and not just a text value for month

if it is TEXT then you can just use countifs without the <= and >=
just use =B1

But that will count all values not just unique

 

[CraftedMcNulty]

Thanks - this is about as far as I got also... looking for a unique count however not a total count. Is there a way to add the frequency function?

 

[Aladin Akyurek]

Thanks - this is about as far as I got also... looking for a unique count however not a total count. Is there a way to add the frequency function?

Care to post a small sample along with the expected count?