# Trying to count an array with multiple criteria, one of which is if a number is odd....

#### mcs9b

##### New Member
My table basically consists of row 1 corresponding to days of the month (1-31), and each row below represents a person's name in my group. Each day of the month, someone is assigned the value "L". I basically want to count the number of times someone is "L" on an odd day of the month. I'm going to try and represent a simplified version....

A B C D E
1 2 3 4 5
L L L
L
L L

I couldn't put in the peoples names, but obviously the rows would be numbered 1-4. The formula from the person in row 2 would be 2 since an "L" appears on odd days 1 and 3. The person in row 3 would have a result of 1, and the one in row four would also have the result = 1. Can anyone help???

Last edited:

#### martindwilson

##### Well-known Member
Try with days of month in b1-af1
=sumproduct(--(mod(\$b\$1:\$af\$1,2)=1),--(b3:ag3="L"))

Last edited:

#### mcs9b

##### New Member
Neither of those suggestions worked. Is there some way of using =count(if), including the isodd() function? I tried this but couldn't get it to work, (I don't think the isodd funciton works with ranges/arrays). Any other suggestions?

Last edited:

#### pgc01

##### MrExcel MVP
The solutions posted work for me.

In A5:

=SUMPRODUCT((MOD(\$C\$4:\$I\$4,2)<>0)*(C5:I5="L"))

Copy down

(I don't think the isodd funciton works with ranges/arrays)
In excel 2007 it works with arrays.

#### mcs9b

##### New Member
Thank you! That worked perfectly.

1,082,280
Messages
5,364,210
Members
400,786
Latest member
ismi88

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...