count if for more than one criteria

[Gerardo]

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?

 

[Roger Govier]

Hi Gerardo

I have no idea what 0.2 means
If it means Month 2 then try the following
=SUMPRODUCT(($A$1:$A$1000="New")*(MONTH(($B$2:$B$100)=2))

 

[Gerardo]

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.

 

[Pete_UK]

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

 

[Gerardo]

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

 

[Roger Govier]

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