I have formula, but need better one

[Guest]

This is a sheet that I have telling which month I need to contact certain people in a training program. Row one needs contacted in april, row 2 in march, row 3 in march, and so on
I need a formula to put on a different sheet that counts how many are due in each month of the year, but the trick is I don't want a month counted if it is skipped such as in row 5 for december because it has already passed

A B C D E
1 5/7/2003 6/9/2003 7/3/2003 10/8/2003 1/12/2004 ap
2 mar apr may aug nov fe
3 2/19/2004 mar apr jul oct ja
4 2/2/2004 mar apr jul oct ja
5 11/18/2003 dec 1/27/2004 apr jul oc

 

[Frank Kabel]

Hi Ben
your example is (at least for me) quite confusing. I'm not sure what
the different rows / columns store. Maybe you can exmplain this with
more detail??

 

[Don Guillett]

How are we to know that your formula is not the BEST one.

--
Don Guillett
SalesAid Software
(e-mail address removed)

This is a sheet that I have telling which month I need to contact certain

people in a training program. Row one needs contacted in april, row 2 in
march, row 3 in march, and so on.

I need a formula to put on a different sheet that counts how many are due

in each month of the year, but the trick is I don't want a month counted if
it is skipped such as in row 5 for december because it has already passed.

 

[Guest]

It lost the formatting, I will try and fix then explain better. Each row(person) has 6 columns. A,B,C,D,E, and F. In each column I write the month that I need to contact a person such as jan feb mar jun sept dec. When I contact a person I will write the date in for that month 1/12/2004, 2/13/2004, mar, jun, sept, dec. I need to count how many people would be due for each month jan through dec throughout my whole spreadsheet. My problem is that what happens if a person is not contacted and the month is skipped for example 1/12/04, feb, 3/1/2004, jun, sept, dec. I can't write a formula to count all feb's becuase it would be counting skipped one's giving false numbers because Lets say it was already the year 2005 and here I have a skipped feb in 2004 and I counted it.
To make things simple I need a formula to count all jan's, feb's, mar's etc. throughout my spreadsheet but only the one's that don't have an actual date to the left and to the right. If they have one to the left but not the right then count it, or to the right and not the left, then count it. but if it has an actual date to the left and to the right then don't count it because that would be a skipped month. Confusing?

 

[Don Guillett]

something like this, maybe??
=sumproduct((month(b2:b20)=2)*(a2:a20<1)*(b2:b20<1))

--
Don Guillett
SalesAid Software
(e-mail address removed)

It lost the formatting, I will try and fix then explain better. Each

row(person) has 6 columns. A,B,C,D,E, and F. In each column I write the
month that I need to contact a person such as jan feb mar jun sept dec. When
I contact a person I will write the date in for that month 1/12/2004,
2/13/2004, mar, jun, sept, dec. I need to count how many people would be
due for each month jan through dec throughout my whole spreadsheet. My
problem is that what happens if a person is not contacted and the month is
skipped for example 1/12/04, feb, 3/1/2004, jun, sept, dec. I can't write a
formula to count all feb's becuase it would be counting skipped one's giving
false numbers because Lets say it was already the year 2005 and here I have
a skipped feb in 2004 and I counted it.

To make things simple I need a formula to count all jan's, feb's, mar's

etc. throughout my spreadsheet but only the one's that don't have an actual
date to the left and to the right. If they have one to the left but not the
right then count it, or to the right and not the left, then count it. but if
it has an actual date to the left and to the right then don't count it
because that would be a skipped month. Confusing?