Excel help

[sleepless]

Hi all, please need help.

I have an excel example below, how do i create a formula to count the number of full days, holiday, unplanned etc to a person only like tom.

I have done a countif formula which counts all if it matches holiday but cannot get it down to count holidays to only tom's or tien's row.




Thanks for your help

 

[buxtongt]

That is going to make it very difficult to define the ranges like you need for the countif function. There is probably a way to do it with a loop in a macro or something else fun like that, but I think that's all over my head. I'm sure some of the real pros here can give you a hand. Best of luck.

 

[sleepless]

Thanks for trying buxtongt

 

[buxtongt]

Hmmm... if you did the countif function for each row (make the range a1:a5 for example) you could then filter by name and add up the total from each row, or use the subtotal function to add it up as you filter. You would still have to pull each name to get the total though. I'll keep thinking.

 

[Rick Rothstein]

Does this formula do what you want?

=SUMPRODUCT((A2:A999="Tien")*(C2:I999="Full day"))

The 999 represents a row number equal to or larger than the maximum row number you ever expect to put data in (change it to meet your specific future needs). Obviously, change "Tien" to "Tom" to count his full days. Also, if more convient, you can put the name in a cell, say for example purposes, J1 and then use that directly in the formual in place of the name. Also, if you want to search for something other than "Full day", then just change that part of the formula as necessary.

 

[sleepless]

Massive thanks Rick Rothstein, you have saved me hours of work every month