# Count function based on multiple columns

1. ## Count function based on multiple columns

Working on a formula I thought would be easy but it is perplexing me.
The attached workbook has 3 columns and trying to count unique occurrences in Column E (Products Delivered) based on values in columns C and D.

I thought COUNTIFS could work but each criteria needs a value to search for. I thought SUMPRODUCT might work but it won't identify the unique value number I am looking for?

2. ## Re: Count function based on multiple columns

Here is a Power Query Solution

Excel 2016 (Windows) 32 bit
A
B
C
1
Vendor Month Due Count
2
Acme July
4
3
Acme August
3
4
Bander May
3
5
Acme May
2
6
Acme March
2
 Sheet: Table1

``Please Login or Register  to view this content.``
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for a video which demonstrates how to use Power Query code provided.

3. ## Re: Count function based on multiple columns

What version of Excel are you using? I suggest you update your profile to show the version.
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Count function based on multiple columns

G6=SUM(IF(FREQUENCY(IF(\$C\$4:\$C\$43=G4,IF(D4:D43=G5,MATCH(E4:E43,E4:E43,0))),ROW(E4:E43)-ROW(E4)+1),1))

Control+shift+enter

5. ## Re: Count function based on multiple columns

I like it! I am using 365 and tried the UNIQUE(FILTER option but that returned the range. I don't think I have ever seen the ROWS function before. Thank you!

6. ## Re: Count function based on multiple columns

Thank you for the response! I have never used power query but given my recent question, I need to learn. Thank you for the response!

7. ## Re: Count function based on multiple columns

The formula I suggested return 4 for me. What does it return for you?

There are currently 1 users browsing this thread. (0 members and 1 guests)