Urgent question- count number of SKUs without duplicate with 3 conditions

[ssj3010]

Hi

I have a table with multiple columns and I want to calculate the number of SKUs using their codes without counting the duplicates with the condition of this sku being small griller, regular sku and in june

my table looks like this

month sku code sku type family 2
jan 1234 regular medium griller
jun 1235 regular small griller
jun 1235 promo small griller
feb 1235 regular small griller
jun 1235 regular small griller
how can i know how many regular small griller in june are there ?

 

[sheetspread]

Excel 2013 has a distinct count in the pivot table (not sure how many fields it can handle), or you can concatenate with the & operator in a helper column then countif. Power Query in BI Tools (or MS Query which all 2003 and after Excel versions have) can show only unique rows and count them too. A multi condition frequency formula is possible but not the best solution.

 

[63falcondude]

Assuming your data looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]month[/TD]
[TD="align: center"]sku code[/TD]
[TD="align: center"]fsku type[/TD]
[TD="align: center"]family 2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]jan[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]regular[/TD]
[TD="align: center"]medium griller[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]jun[/TD]
[TD="align: center"]1235[/TD]
[TD="align: center"]regular[/TD]
[TD="align: center"]small griller[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]jun[/TD]
[TD="align: center"]1235[/TD]
[TD="align: center"]promo[/TD]
[TD="align: center"]small griller[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]feb[/TD]
[TD="align: center"]1235[/TD]
[TD="align: center"]regular[/TD]
[TD="align: center"]small griller[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]jun[/TD]
[TD="align: center"]1235[/TD]
[TD="align: center"]regular[/TD]
[TD="align: center"]small griller[/TD]
[/TR]
</tbody>[/TABLE]

and you want to find the number of regular small grillers in jun, use the formula

=COUNTIFS(A:A,"jun",C:C,"regular",D:D,"small griller")

Edit: misread the question. This counts the number of regular small grillers in June. Has nothing to do with SKU.

 

[63falcondude]

You can add a helper column E labeled sku code count with the formula in E2

=COUNTIF($B$2:B2,B2)

Now just add another condition to the countifs formula where column E is equal to 1. That would make it

=COUNTIFS(A:A,"jun",C:C,"regular",D:D,"small griller",E:E,1)

 

[AhoyNC]

If your SKU's are actual numbers try the formula in B12, if they are text then the formula in D12.

Both are array formulas and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

Excel Workbook
	A	B	C	D
1	month	sku code	fsku type	family 2
2	jan	1234	regular	medium griller
3	jun	1235	regular	small griller
4	jun	1235	promo	small griller
5	feb	1235	regular	small griller
6	jun	1235	regular	small griller
7
8	Find
9	month	sku	fsku type	family 2
10	jun	1235	regular	small griller
11
12	count	1		1
Sheet

 

[ssj3010]

my problem is that my table has small griller for example has multiple codes. so I want each code even if repeated hundred times to e counted only once. How can I please do that? I dont know if there is a way to insert a file because it is easier? I have been stuck for two days with this

If your SKU's are actual numbers try the formula in B12, if they are text then the formula in D12.

Both are array formulas and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

*	A	B	C	D
jan	regular	medium griller
jun	regular	small griller
jun	promo	small griller
feb	regular	small griller
jun	regular	small griller
*	*	*	*
*	*	*
*	*	*	*
count	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:77px;"><col style="width:99px;"><col style="width:111px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]month[/TD]
[TD="bgcolor: #c0c0c0"]sku code[/TD]
[TD="bgcolor: #c0c0c0"]fsku type[/TD]
[TD="bgcolor: #c0c0c0"]family 2[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: center"]1234[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: center"]1235[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: center"]1235[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: center"]1235[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: center"]1235[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #c0c0c0"]Find[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #c0c0c0"]month[/TD]
[TD="bgcolor: #c0c0c0"]sku[/TD]
[TD="bgcolor: #c0c0c0"]fsku type[/TD]
[TD="bgcolor: #c0c0c0"]family 2[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #99cc00"]jun[/TD]
[TD="bgcolor: #99cc00, align: center"]1235[/TD]
[TD="bgcolor: #99cc00"]regular[/TD]
[TD="bgcolor: #99cc00"]small griller[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #ffff00, align: center"]1[/TD]

[TD="bgcolor: #ffff00, align: center"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B12 {=SUM(IF(FREQUENCY(IF($B$2:$B$6<>"",IF($B$2:$B$6=$B$10,IF($A$2:$A$6=$A$10,IF($C$2:$C$6=$C$10,IF($D$2:$D$6=$D$10,$B$2:$B$6))))),$B$2:$B$6),1))} D12 {=SUM(IF(FREQUENCY(IF($B$2:$B$6<>"",IF($B$2:$B$6=$B$10,IF($A$2:$A$6=$A$10,IF($C$2:$C$6=$C$10,IF($D$2:$D$6=$D$10,MATCH($B$2:$B$6,$B$2:$B$6,0)))))),ROW($B$2:$B$6)-ROW($B$2)+1),1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ssj3010]

the problem is small griller has multiple codes that are repeated more than once

You can add a helper column E labeled sku code count with the formula in E2

=COUNTIF($B$2:B2,B2)

Now just add another condition to the countifs formula where column E is equal to 1. That would make it

=COUNTIFS(A:A,"jun",C:C,"regular",D:D,"small griller",E:E,1)

 

[sheetspread]

my problem is that my table has small griller for example has multiple codes. so I want each code even if repeated hundred times to e counted only once. How can I please do that? I dont know if there is a way to insert a file because it is easier? I have been stuck for two days with this

AhoyNC's formula does that, or is easily modified for the exact purpose.

Here's how to post samples: http://www.mrexcel.com/forum/about-board/947826-why-cant-i-add-attachments-my-threads.html

And here's a similar problem solved with MS Query:

http://www.mrexcel.com/forum/excel-...s-do-columns-have-how-many-2.html#post4334421

 

[Aladin Akyurek]

What is the expected value for the sample you posted in post #1?

 

[sheetspread]

Also the advanced filter, which will return unique records, and the remove duplicates function (not always recommended because of its bugs). Together with a countifs function can also have the desired results.