Count of multiple criteria

[AliG999]

I hope you can help

I am running out of time and I am trying to run a formula which provides a count based on 2 types of criteria Year (Column A) & Type (Column S - which is a multiple pick list option)

I have tried

=COUNTIFS('SALES'!A2:A100,2012,'SALES'!S2:S100,"*Family*")
=SUMIFS('SALES'!A2:S100, 'SALES'!A2:A100,2012,'SALES'!S2:S100,"Family")

Both are returning a Value error. I know this will be something simplistic that I'm missing out so any help gratefully appreciated!

Thanks in advance!

Ali

 

[RoryA]

Re: Count of multiple criteria - Urgent

Do you have any #VALUE errors in either of those columns?

 

[Aladin Akyurek]

Re: Count of multiple criteria - Urgent

Do you have any [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] errors in either of those columns?

That should not be a problem, unless every cell contains a #VALUE ! error.

 

[uswyne]

Re: Count of multiple criteria - Urgent

can u plz show the table. for better understanding

 

[Aladin Akyurek]

Re: Count of multiple criteria - Urgent

=COUNTIFS('SALES'!A2:A100,2012,'SALES'!S2:S100,"*Family*")

should succeed unless you have errors in every cell.

=SUMIFS('SALES'!A2:S100, 'SALES'!A2:A100,2012,'SALES'!S2:S100,"Family")

The bolded marix range is not allowed here. The range to sum must have the same shape as the ranges which are evaluated against some criteria.

 

[AliG999]

Re: Count of multiple criteria - Urgent

There's no value error in either column as both have data in them in all cells in the range. I know that I have 15 rows from 2012 - 2 of which contain 'Family' in the Type column.

Hence why I'm stumped

 

[AliG999]

Re: Count of multiple criteria - Urgent

Unfortunately I can as the data is on a stand alone pc and I'm going from 1 desk to another. It looks similar to below:

COLUMN A COLUMN B COLUMN C ... COLUMN S
Year Area Discount Type

2012 Dublin Yes/No Family, Colleague, Damaged etc

Not sure if that helps?

Ali

 

[AliG999]

Re: Count of multiple criteria - Urgent

=COUNTIFS('SALES'!A2:A100,2012,'SALES'!S2:S100,"*Family*")

should succeed unless you have errors in every cell.

=SUMIFS('SALES'!A2:S100, 'SALES'!A2:A100,2012,'SALES'!S2:S100,"Family")

The bolded marix range is not allowed here. The range to sum must have the same shape as the ranges which are evaluated against some criteria.

Thanks Aladin I will bear that in mind

The fact that the cell I am referencing for 2012 is a drop down & therefore not a constant affect matters? If so how do I overcome this?

Thanks

Ali

 

[AliG999]

Re: Count of multiple criteria - Urgent

Got it to work thank you using the CountIfs formula & changing the reference cell to one that provided a unique year number.

Many Thanks

Ali