sumproduct of 2 columns with date and name

  • Thread starter excel guru i''m not
  • Start date
E

excel guru i''m not

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"))
 
D

Dave Peterson

Why do you say it doesn't work?


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"))
Click to expand...
 
E

excel guru i''m not

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.
 
E

excel guru i''m not

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.
 
E

excel guru i''m not

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.
 
P

pinmaster

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

Regards
J
 
E

excel guru i''m not

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.
 
P

pinmaster

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

Regards
JG
 
P

Peo Sjoblom

Another way

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

--
Regards,

Peo Sjoblom

(No private emails please)
 
Top