Sum range of numbers based on Name and Month

**Toli** · 07-25-2012, 02:08 PM

Please help...
I'm trying to sum numbers in a column based on a range of Months & Names. I recently received a formula that counts the number of times a name appears in a month works great: =SUMPRODUCT(--($K$17:$K$29=$S20),--(TEXT($M$17:$M$29,"mmmyyyy")=TEXT(T$19,"mmmyyyy")))
I'm trying to sum total hr's so I can see how many jobs an employee takes and the time it took to complete that number of jobs per month. I've tried to use the above code sent to me by one of your members by revising it by adding count, countif, etc. so many different tries. The closest I gotten was summing up all of the same names or I get "=name" or "0".

Data sample:
rng1...rng2...rng3
a..........b........c
joe2 2 23-Feb
joe1 3.5 16-Apr Joe1 month of Apr = 6 (day doesn't matter)
joe1 2.5 16-Apr
joe1 3 2-May Joe2 month of Feb = 2 (day doesn't matter)
joe3 2 27-Oct Joe3 month of oct = 2 (day doesn't matter)

If you can help please explain how the formula works.

**Pepe Le Mokko** · 07-25-2012, 02:51 PM

Perhaps use a Pivot Table grouping dates per month?

**Toli** · 07-26-2012, 10:33 AM

Sorry it took so long to reply I'm a facilities guy and on the road alot. I haven't used pivot tables and am hoping to make this as simple as possible I have 13 separate places I need to run this code. Then group all the info on another spreadsheet. Thanks...

---------- Post added at 10:33 AM ---------- Previous post was at 10:25 AM ----------

Hi Haseeb, Wow all the way in Kuwait hope all is good there... I'm a facilities guy so out on the road a lot replys could be a little slow. I've tried what I thought was everything but I didn't think of adding a sum range that way, it couldn't be that simple could it. I'll give it a try and let you know if I've accomplished the task.

Thanks for the idea's I could use it.

**Haseeb Avarakkan** · 07-25-2012, 09:26 PM

Add one more range in SUMPRODUCT.

=SUMPRODUCT(--($K$17:$K$29=$S20),--(TEXT($M$17:$M$29,"mmmyyyy")=TEXT(T$19,"mmmyyyy")),SumRange)

**Toli** · 07-27-2012, 03:30 PM

Hi Haseeb,
I tried your suggestion by adding a SumRang (sum, Sumif, sumifs) but it just doesn't work. I put the formula together a number of way and can't get it to add up correctly. I can make it add up all the Joe1 Joe2 or Joe3 but not just same month I get "0" or "-" or some large number(probably due to adding the date equivalents. But I'm not getting an error so there is something else going on I don't understand. do you have any other ideas. I could surely use the help.

**Haseeb Avarakkan** · 07-27-2012, 04:12 PM

Could you please attach a sample file, replace all confidential data with dummy. To attach a file on bottom click 'Go Advanced' then 'Manage Attachments' top right side 'Add Files' & follow the instructions.

**Toli** · 07-27-2012, 08:34 PM

I just wrote a detail but when I tried to attache the system asked me for my login again and put me to nowhere. Had to log back in and lost all my detail... So I put some detail on the excel sample hope it's enough for you to understand. This is part of a much larger file.

Thanks again for all your efforts to help.

regards
Toli.

**Haseeb Avarakkan** · 07-27-2012, 08:42 PM

I20, copy across & down.

=SUMPRODUCT(--($C$13:$C$25=$H20),--(TEXT($E$13:$E$25,"mmmyyyy")=TEXT(I$19,"mmmyyyy")),$D$13:$D$25)

works for me.

Edit:

Since you are on Excel 2010 SUMIFS will be faster than SUMPRODUCT.

=SUMIFS($D:$D,$C:$C,$H20,$E:$E,">="&EOMONTH(I$19,-1)+1,$E:$E,"<="&EOMONTH(I$19,0))

**Toli** · 08-04-2012, 04:03 PM

Haseeb,
Thank you and sorry for the slow reply I've been traveling took your formula entered it to my spread sheet and it does indeed giving me what I needed. Using the &EO in the sumifs is another neat trick for me thank you. Some of these expressions I've never had the reason to need but now that I do this is really what I need. I'm going with the =Sumproduct since I started with that but thanks for the Sumifs. When you told me to put a sum range the last comunique I did but my mistake was to add the Word Sum in the statement with parenthesis and not exactly the way you ment for me to write the statement )),$D$13:$D$25).

This will go a long way in allowing me to track the progress of my employees... I added a graph based on the HR's and Items which is giveing me a real picture of individual progress.

Thank you again.

---------- Post added at 04:03 PM ---------- Previous post was at 03:59 PM ----------

Haseeb,
Thank you and sorry for the slow reply I've been traveling took your formula entered it to my spread sheet and it does indeed giving me what I needed. Using the &EO in the sumifs is another neat trick for me thank you. Some of these expressions I've never had the reason to need but now that I do this is really what I need. I'm going with the =Sumproduct since I started with that but thanks for the Sumifs. When you told me to put a sum range the last comunique I did but my mistake was to add the Word Sum in the statement with parenthesis and not exactly the way you ment for me to write the statement )),$D$13:$D$25).

This will go a long way in allowing me to track the progress of my employees... I added a graph based on the HR's and Items which is giveing me a real picture of individual progress.

Thank you again.

Replied again not sure if you received the last replay... this site confuses me sometimes...