This is the formula I am using but it doesn't work.

any suggestions?

I am trying to add the number of times that in one column it says any date
of january and in the other column it says Tom. Not counting any blank rows
in the date column.

=SUMPRODUCT(--(TEXT($J$4:$J$97,"mmm")="jan"),--($J$4:$J$97<"")*($O$4:$O$97="tom"))

Why do you say it doesn't work?



excel guru i''m not wrote:

This is the formula I am using but it doesn't work.

any suggestions?

I am trying to add the number of times that in one column it says any date
of january and in the other column it says Tom. Not counting any blank rows
in the date column.

=SUMPRODUCT(--(TEXT($J$4:$J$97,"mmm")="jan"),--($J$4:$J$97<"")*($O$4:$O$97="tom"))

--

Dave Peterson

Try:

=SUMPRODUCT((MONTH(J4:J97)=1)*(O4:O97="tom"))

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497159

I'm getting a response of zero, right now all but a few rows have january
dates in them and the column with tom has 4.

"Dave Peterson" wrote:

Why do you say it doesn't work?



excel guru i''m not wrote:

This is the formula I am using but it doesn't work.

any suggestions?

I am trying to add the number of times that in one column it says any date
of january and in the other column it says Tom. Not counting any blank rows
in the date column.

=SUMPRODUCT(--(TEXT($J$4:$J$97,"mmm")="jan"),--($J$4:$J$97<"")*($O$4:$O$97="tom"))

--

Dave Peterson

I found the problem. The column says tommy instead of tom. So with tommy in
the formula it worked. You are way to smart for me. I guess you knew it
should have worked.

"Dave Peterson" wrote:

Why do you say it doesn't work?



excel guru i''m not wrote:

This is the formula I am using but it doesn't work.

any suggestions?

I am trying to add the number of times that in one column it says any date
of january and in the other column it says Tom. Not counting any blank rows
in the date column.

=SUMPRODUCT(--(TEXT($J$4:$J$97,"mmm")="jan"),--($J$4:$J$97<"")*($O$4:$O$97="tom"))

--

Dave Peterson

Ok so maybe you can help me with one more in this same realm. I have the
date column and the column with tom, but wes is also in that column and so is
wesandtommy. Based on the tom formula I can count wes by chaning tom in the
formula to wes. But how would I count the ones that say wesandtom giving
each of them half credit and putting the answer in the same place as the
formula for just their name.

"Dave Peterson" wrote:

Why do you say it doesn't work?



excel guru i''m not wrote:

This is the formula I am using but it doesn't work.

any suggestions?

I am trying to add the number of times that in one column it says any date
of january and in the other column it says Tom. Not counting any blank rows
in the date column.

=SUMPRODUCT(--(TEXT($J$4:$J$97,"mmm")="jan"),--($J$4:$J$97<"")*($O$4:$O$97="tom"))

--

Dave Peterson

=SUMPRODUCT((MONTH(J4:J97)=1)*(O4:O97="tom"))+SUMP RODUCT((MONTH(J4:J97)=1)*(O4:O97="wesandtom"))/2

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497159

Thank you so much. That completes a worksheet I have been working on for an
entire week. Mostly because I don't know what I'm doing.

Now I have just one more thing. Now that my sheet is complete I have some
extra lines. How do I delete and add rows and columns and have my formulas
automatically adjust to want is being added or taken away.

"pinmaster" wrote:


=SUMPRODUCT((MONTH(J4:J97)=1)*(O4:O97="tom"))+SUMP RODUCT((MONTH(J4:J97)=1)*(O4:O97="wesandtom"))/2

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497159

They will ajust automatically, test it and see for yourself.
You might want to test it on a copy just in case.

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497159

Another way

=SUMPRODUCT((MONTH(J4:J97)=1)*(O4:O97={"tom","wesa ndtom"})*({1,0.5}))

--
Regards,

Peo Sjoblom

(No private emails please)


"pinmaster" wrote
in message ...

=SUMPRODUCT((MONTH(J4:J97)=1)*(O4:O97="tom"))+SUMP RODUCT((MONTH(J4:J97)=1)*(O4:O97="wesandtom"))/2

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile:
http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497159