count if for more than one criteria

Thank you, the 0.2 are the values I want to evaluate in each element of the
array. Items are in rows and months are in columns, for example item 1 could
be 0.5 in January and 0.2 in February. What I want to sum is all the values
greater than 0.2 for each month.

"Gerardo" wrote:

> For several items listed in rows, I have one column with values "old" and
> "new" . In each column in the array I have months from 01/2004 through
> 12/2014. I want to count values in the array that are more than 0.2 and "new"
> in one row and more than 0.2 and "old" in a second row. The problem is that
> count if only counts either more than 0.2 values or "old" or "new".
>
> Any ideas?

Tell us which columns you are using for the old/new values and for the
values >0.2 - do you have 12 columns, one for each month?

Pete

On Aug 15, 7:08 pm, Gerardo <Gera... DeleteThis @discussions.microsoft.com> wrote:
> Thank you, the 0.2 are the values I want to evaluate in each element of the
> array. Items are in rows and months are in columns, for example item 1 could
> be 0.5 in January and 0.2 in February. What I want to sum is all the values
> greater than 0.2 for each month.
>
>
>
> "Gerardo" wrote:
> > For several items listed in rows, I have one column with values "old" and
> > "new" . In each column in the array I have months from 01/2004 through
> > 12/2014. I want to count values in the array that are more than 0.2 and "new"
> > in one row and more than 0.2 and "old" in a second row. The problem is that
> > count if only counts either more than 0.2 values or "old" or "new".
>
> > Any ideas?- Hide quoted text -
>
> - Show quoted text -

Hello,

Column 1 keeps names for each item, column 2 keeps old/new values and the
rest of columns are months from Jan-2004 to Dec-2014.

A B C D E
1 Name Type Jan-2004 Feb-2004 Mar-2004
2 Item 1 Old 0.545 0.454 0.152
3 Item 2 New 0.030 1.354 0.854
4 Item 3 New 1.256 0.600 0.400

Count new items greater than 0.2 each month
Count old items greater than 0.2 each month

Regards

"Pete_UK" wrote:

> Tell us which columns you are using for the old/new values and for the
> values >0.2 - do you have 12 columns, one for each month?
>
> Pete
>
> On Aug 15, 7:08 pm, Gerardo <Gera... RemoveThis @discussions.microsoft.com> wrote:
> > Thank you, the 0.2 are the values I want to evaluate in each element of the
> > array. Items are in rows and months are in columns, for example item 1 could
> > be 0.5 in January and 0.2 in February. What I want to sum is all the values
> > greater than 0.2 for each month.
> >
> >
> >
> > "Gerardo" wrote:
> > > For several items listed in rows, I have one column with values "old" and
> > > "new" . In each column in the array I have months from 01/2004 through
> > > 12/2014. I want to count values in the array that are more than 0.2 and "new"
> > > in one row and more than 0.2 and "old" in a second row. The problem is that
> > > count if only counts either more than 0.2 values or "old" or "new".
> >
> > > Any ideas?- Hide quoted text -
> >
> > - Show quoted text -
>
>

Hi

Insert 2 new rows above your Header row.
In the new B1 type Old
In the new B2 type New
In cell C1 enter
=SUMPRODUCT(($B$4:$B$1000=$B1)*(C$4:C$1000>0.2))
Copy formula down to C2
Copy C1:C2 across the sheet as far as you wish

--
Regards
Roger Govier

"Gerardo" <Gerardo.DeleteThis@discussions.microsoft.com> wrote in message
news:B0075900-9EC7-4944-8412-7E16969C9D08@microsoft.com...
> Hello,
>
> Column 1 keeps names for each item, column 2 keeps old/new values and the
> rest of columns are months from Jan-2004 to Dec-2014.
>
> A B C D E
> 1 Name Type Jan-2004 Feb-2004 Mar-2004
> 2 Item 1 Old 0.545 0.454 0.152
> 3 Item 2 New 0.030 1.354 0.854
> 4 Item 3 New 1.256 0.600 0.400
>
> Count new items greater than 0.2 each month
> Count old items greater than 0.2 each month
>
> Regards
>
> "Pete_UK" wrote:
>
>> Tell us which columns you are using for the old/new values and for the
>> values >0.2 - do you have 12 columns, one for each month?
>>
>> Pete
>>
>> On Aug 15, 7:08 pm, Gerardo <Gera....DeleteThis@discussions.microsoft.com> wrote:
>> > Thank you, the 0.2 are the values I want to evaluate in each element of
>> > the
>> > array. Items are in rows and months are in columns, for example item 1
>> > could
>> > be 0.5 in January and 0.2 in February. What I want to sum is all the
>> > values
>> > greater than 0.2 for each month.
>> >
>> >
>> >
>> > "Gerardo" wrote:
>> > > For several items listed in rows, I have one column with values "old"
>> > > and
>> > > "new" . In each column in the array I have months from 01/2004
>> > > through
>> > > 12/2014. I want to count values in the array that are more than 0.2
>> > > and "new"
>> > > in one row and more than 0.2 and "old" in a second row. The problem
>> > > is that
>> > > count if only counts either more than 0.2 values or "old" or "new".
>> >
>> > > Any ideas?- Hide quoted text -
>> >
>> > - Show quoted text -
>>
>>