Calculate With Criteria

**Kumara_faith** · 04-01-2008, 06:51 AM

Hi,

I have the following table which I need to summarize the data based on productivity data by staff ID , day and hours.

Example, for staff M1, the total data for "LM" for all Mondays in the month of March based on table 1 is summarized on table 2 in cell C23

Similalarly, total "Hours" worked for staff M1 for all Mondays in the month of March based on table 1 is summarized on cell D23 in table 2.

I am trying to use sumproduct formula to generate this data in table 2 based on table 1 but it does not work.

Appreciate help.

**EdMac** · 04-01-2008, 07:33 AM

I don't think you can use sumproduct in this manner

You will need to do it as a SUM

e.g. in C24=SUM(C6,M6...........etc)

**NBVC** · 04-01-2008, 08:11 AM

If you want to use sumproduct, then the best thing to do is unmerge the merged cells and duplicate the headings.

see attached

**Kumara_faith** · 04-01-2008, 09:35 AM

NBVC,

Thank you for your solution.

The figures on cell D24 actually should read as 25 but it shows as 149.25.

I checked and I think the formula is right but I am not sure why it returns as 149.25 .

Appreciate your review.

**EdMac** · 04-01-2008, 10:26 AM

Nice solution NBVC - much better than what I could do!

BUT

Need to change some refs - NBVC has managed to include the total cols at the far right so there is double counting - Find and replace AR for AT and it looks OK

**NBVC** · 04-01-2008, 10:39 AM

Originally Posted by Kumara_faith

NBVC,

Thank you for your solution.

The figures on cell D24 actually should read as 25 but it shows as 149.25.

I checked and I think the formula is right but I am not sure why it returns as 149.25 .

Appreciate your review.

Sorry, When I changed the titles to Unmerge...somehow I got another Mon in AT3... Change Cell AT3 to read "Total" and it should fix it.