LOOKUP & SUM cells with text and numbers in

[JCTalk]

Hi Guys,

Another new project I'm working on to view attendance and absence.

A lot of the things I need to do on this are much the same, I just need to find out how to do the below, then I can repeat it for the other things.

1. For attendance, I need to COUNT the number of cells with "•" and "C" in, where the MONTH and TEAM criteria are met. I considered =IF(Q2=B4:B40,IF(P2=D2:N2,SUM(COUNTIF(D4:N40,"•"),COUNTIF(D4:N40,"C")))) but this results in FALSE. I think it's the IF's that are failing though because the last part seems to work.
   
   
2. For Sickness and AL, I need to SUM the numerical value in each cell (hours) together where the MONTH, TEAM and SICK/AL criteria are met. I somehow need to split the number from the text to sum it.

I need to do both these things as a single cell formula with no helper cells. I put my expected values in the attached workbook.

Any help that can be provided would be very much appreciated.

Thank you guys and girls.

 

[Hui]

I would use a Sumproduct based formula like this in R3 (Your R2)
=SUMPRODUCT((($D$4:$N$40="•")+($D$4:$N$40="C"))*($D$2:$N$2=$P3)*($B$4:$B$40=$Q3))

see attached file:

I have added a table for Count and a table for Sum


see attached file:

You can read about how these Sumproduct functions work here:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[JCTalk]

I would use a Sumproduct based formula like this in R3 (Your R2)
=SUMPRODUCT((($D$4:$N$40="•")+($D$4:$N$40="C"))*($D$2:$N$2=$P3)*($B$4:$B$40=$Q3))

Hi Hui,

Fantastic! Thank you. SUMPRODUCT works great for that part.

Any thoughts on the second part, SUM'ing the numerical value in each cell (hours) together where the MONTH, TEAM and SICK/AL criteria are met. I somehow use the text part as criteria to lookup, and split the number from the text and SUM it.

Or should I raise this as a separate question in the forum?

Many thanks Hui.