Sumifs Multiple criteria 1

[Topcat04]

Hi Forum

Can you please advise how to modify the Sumifs variable to include multiple criteria on the same range.

Eg.

=SUMIFS(Sales, Variant,"="&$O$6, Variant,"="&$O$12, Variant,"="&$O$13, Variant,"="&$O$16, Variant,"="&$O$28, Format,"="&$Q7)

Cell O6,O12,O13,O16,O28 all contain variants that need to be used together to get to the final total.

Thank you!

 

[SkipVought]

hi,

The SUMIFS() function means that EACH criteria MUST be present on the row,

Obviously that cannot happen, for Variant to have 5 different values on the same row!

You really want an OR, not an AND for these Variant values.

So you must have a separate SUMIFS() function for each criteria and ADD the SUMIFS()

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Thanks Skip

 

[SkipVought]

I meant deparate SUMIFS() for each Variant criteria,like...
[tt]

=SUMIFS(Sales, Variant,"="&$O$6, Format,"="&$Q7)+SUMIFS(Sales, Variant,"="&$O$12, Format,"="&$Q7)+.....


[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Thanks again Skip

 

[Topcat04]

Skip

How would I do this if I wanted to sum the number of unique product categories instead of summing the sales for the same data.

The data is a 12 month period, so there are the same categories sold each month, but I would like to count the number of products sold in each category under each variant.

For example,

there are hats, coats, scarfs, gloves etc, but then these fall under wool, leather, nylon, thermal etc sold each month, but the data is for the full year with dates set out as 201101, 201102 etc

So I am trying to understand how many of each of the categories (hats, scarfs etc) and in each variant (wool, leather etc) but only counting them once.

so I could end up with 100 wool gloves, 75 leather gloves, 20 thermal gloves etc

Hope this explains it well enough..

Thanks!

 

[Gavona]

Pivottable maybe easiest.


Gavin

 

[SkipVought]

I agree with Gavin.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Just to add, if you use a pivot table but can't get the presentation that you need then post back. At worst you would have to use =getpivotdata(.....) in place of your Sumifs.

Gavin

 

[Topcat04]

Hi Gavin

I got the pivottable working, and it sums all the categories.

How do I get it so that it does not sum duplicates and only unique entries?

thanks for your help!

 

[Gavona]

It is quite hard to understand your requirement. A simple example of your data and what you want to see would be good. Is it as simple as either: Modifying the pivot to count instead of sum? or to count the rows in your pivot table?



Gavin

 

[Topcat04]

Hi Gavin
Here below is the PivotTable, sorry Im not sure how to post as comma seperated code, but hopefully you can get the jist.

Row Labels Count of Format
BATTERY PACK/DOCK 67
CABLES 7
CAR ACCESSORY 126
CHEAT CART 59
COMBINATION PACKS 1898
GAMES CARRY CASE 257
GUITAR 10
HARDWARE CASE/SKIN 2225
HEADPHONES 32
MAINS CHARGER 65
MEDIA PLAYER 1
NETWORK ADAPTORS 12
SCREEN PROTECTOR 126
SPEAKERS 19
STAND 1
STYLUS 504
TOY 1
VIRTUAL CONTROLLER 30
VOICE ACCESS HEADSET 23
Grand Total 5463


So this is counting all of the entries under each category.

However there are not 2225 unique entries under Hardware Case/Skin as this is counting the same entries for the past 10 months.

Main barcode	Product title	Format	Variant	Company	Label	Sales	Price - RRP	Price - ASP	Sales - value	Sales - revenue	Year and month

these are the headers that run along the top of the table.
and under neath is a set of data for each product by month.

as they are monthly sales, the same item can be sold in January, February, March, April etc etc, and instead of being counted as 4, this unique product under Hardware Case/Skin should only be counted as one.

I hope that helps explain it better...

If not, I can share some actual data, just need to understand how to post it properly in here.

Thanks for your help and support.

 

[SkipVought]

Im not sure how to post as comma seperated code

Easy! SaveAs a .csv with your sheet active.

Open the .csv in NOTEPAD not Excel.

COPY 'n' PASTE

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Thanks Skip!

So here is a snippet that will hopefully help explain.

Product title,Format,Variant,Sales,Price - RRP,Price - ASP,Sales - value,Sales - revenue,No. Selling shops,Total shops,Perc. Selling shops,Year and month
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201103
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201104
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201104
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201105
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201105
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201106
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201106
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201107
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201107
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201108
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201108
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201109
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201109
3DS CARRYING CASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201110
3DS BEAGLE PUPPY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201110
3DS BABY MONKEY SLIPCASE,HARDWARE CASE/SKIN,3DS,1,1.00,1.00,1,1,1,1,1.00,201110
,,,,,,,,,,,

Here you can see there are 16 lines all under Hardware Case/Skin, but only 3 Unique entries.

The pivot table I have used counts all 16, is there a way to only count unique entries?

Thanks for helping!

 

[SkipVought]

Drag your Format & Variant to the left. COUNTA the Format column, subtracting the number of heading/totals


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

hi Skip

I have not been able to find COUNTA as an option in the values section of the pivot table?

can you help point in the right direction please?
thanks!

 

[SkipVought]

Use COUNTA() outside the pivottable on a COLUMN range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

Sorry for being a novice user, this is the first time I have used Pivot tables and Im afraid your losing me.

counta() im familiar with, Im not sure how to combine it with a pivottable.

Really appreciate your help, apologise for not picking it up quite as quickly as maybe we should.

 

[SkipVought]

Forget that it's a PIVOT table. It's a TABLE. Let's assume it starts in A1
[tt]
Format Variant Product title
HARDWARE CASE/SKIN 3DS 3DS BEAGLE PUPPY SLIPCASE
3DS CARRYING CASE
3DS BEAGLE PUPPY SLIPCASE
3DS CARRYING CASE
.....
[/tt]
So with the ROW Heading change as illustrated, =COUNTA(A:A) would return 2. Subtract 1 for the heading, assuming that you have no column totaling.

Isn't that what you were looking for, a count of unique Format occurrences?

Alternatively, you can use an ARRAY formula on the Source Data like...
[tt]
=sum(if(frequency(match(format,format,0),match(format,format,0))>0,1))
[/tt]
using Named Ranges.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

hi Skip
thanks for the help, again!

Im actually after a count of unique product titles that fall under the Hardware Case/Skin Format.

The actual data contains 20+ different variants, eg 3DS, DSi, Wii, Xbox etc and there are Hardware cases under each variant.

I think I may have confused things by only giving you a snippet of the data due to thinking that there was a way of doing it in the Pivot Table solution, which I can filter the variants on to the ones that I need at the time,

So is there a way of counting unique products, that fall under the Hardware Case/Skin Format and 3DS variant from the table of data.

Thanks for your continued patience with me.