complicated sumproduct and index

[jimbosi]

Excelforum help.xlsx

Greetings. I have what I think is a stumper (to me). I want to count report serials issued by graders to departments. I've enclosed a sheet.

The Department sheet has the listed departments where I want to do the counting by month. On the second sheet Reports, are the report serials issued by department by date. The third sheet Evaluations contains the referenced report serials and grades.

The first sheet is populated with the numbers I'd like to see per month. What's confounding me is the multiple values per cell in the Evaluations sheet. Many thanks in advance for all the help.

[ChemistB]

How do you get 4 evaluations for 201 in March? I count 3.

[jimbosi]

There's one in Apr, but it points back to a Mar report. Thanks for looking at what I have!

[AZ-XL]

Hi
Used Helper column in Reports sheet
d2 = =SUM(IF(ISERROR(SEARCH(B2,Evaluations!$B$2:$B$7)),0,1)*(MONTH(Reports!C2)=MONTH(Evaluations!$D$2:$D$7))) CSE

In departments sheet changed column names to months.
=SUMPRODUCT((MONTH(D$1)=MONTH(Reports!$C$2:$C$11))*(Departments!$A2=Reports!$A$2:$A$11)*Reports!$D$2:$D$11) CSE then drag druight and down

As ChemistB said, Department 201 gives different number

[jimbosi]

Thank you for your response. I can't say I'm too versed in helper columns.

Department 201 (20100513) counts 4 because one of the Evaluations came in Apr, so I'd like Mar to update (3 from Mar, 1 from Apr) with the additional one. I don't want any Evaluations to fall from the cracks, and Evaluations may come in months after the Department wrote the report. Does that make sense?

At any rate, I greatly appreciate the help.

[AZ-XL]

In Reports file D2 cell
=SUM(IF(ISERROR(SEARCH(B2,Evaluations!$B$2:$B$7)),0,1)*MOD((MONTH(Reports!C2)=MONTH(Evaluations!$D$2:$D$7))+((MONTH(Reports!C2)+1)=MONTH(Evaluations!$D$2:$D$7)),2))

Change this to previous one

[jimbosi]

It looks like that solved it. I'll try it on the live spreadsheet tomorrow. Since it looks like it works, thanks for the help! I would have never figured out the helper column. I'll mark it as solved! Thanks again!

[jimbosi]

I changed it to unsolved. The array formula in the helper column accurately reports back the correct overall number. However, the SUMPRODUCT in the Departments tab doesn't. I have the Date columns formatted as the Date format category. I have the Department pointing to the proper cell. I can't exactly isolate why the results are in error, other than to say I did a sampling of several cells and for those, the results are incorrect. It seems one of the operators in the formula isn't doing a correct calculation.

I'm unable to put the live spreadsheet online. Suffice it to say the helper array formula in the Reports tab appears to be correct (for the overall total), but the SUMPRODUCT in the Departments tab isn't doing the correct calculation. Other than that, we're halfway there! I never would have gotten even that far without the help. Thanks again!

[jimbosi]

I cross-posted this to mrexcel.com http://www.mrexcel.com/forum/excel-q...umproduct.html to get some additional help. I still greatly appreciate this board and the marvelous expertise the gurus have here. Thanks again.