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]

Filter on those values and COUNTA the products. You will have to limit the aggregations to a single one.

Skip,

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

 

[Topcat04]

Hi Skip

I dont think Im getting this right, the Counta counts the number of entries that dont have a blank cell right?

This has over 2000 rows, which unfortunately dont have any blank cells and would be too much to post on here.

the items are repeated sales each month, so Im trying to count the unique records under the Hardware Case over the 10 months YTD.

For example sku 1, sku 2, sku3, sku 4, are sold in Jan, Feb, Mar, Apr etc through to Oct.

Doing a Counta, this returns 40 where as Im only looking for them to be counted once as 'unique' so in this case the entry in January would be the one.

Does that make it any clearer?
thanks!

 

[SkipVought]

the items are repeated sales each month

That is why I stated to " limit the aggregations to a single one." like a COUNT.

Are you looking for a count per month?

It would help if we could see an example of SOURCE date and the associated PT to illustrate the issue.

If it were me, I'd probably use MS Query to return a table of unique values for the aggregation/groupings of interest and do you stats off of that.

But we REALLY need to see a good example.


Skip,

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

 

[Topcat04]

Hi Skip

How would you like me to post the data? With over 2000 lines, this is a bit much to save as csv and post on here?

Is there a better way for you to receive such data?
Thanks sir!

 

[SkipVought]

ii36250

at

bellhelicopter

dot

textron

dot

com

Skip,

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

 

[Topcat04]

Big Thanks to Skip for helping a learning novice!

 

[Gavona]

It's always like that here - not least due to Skip's selfless dedication to helping people learn to do things better.

Gavin

 

[Topcat04]

Thanks go to you as well Gavin! Unfortunately, Skips solution was a little past my learning curve at this stage as it involved SQL queries, which I need to learn!

This forum is a great help to people like us!