MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Linda Jordan on December 11, 2000 8:26 AM

I am new to using excel - am running excel 97:
I have a table in one sheet which contains managers and status.
In another sheet in the same workbook, I need to count the occurrence of status by manager.For example: if manager is equal to: "Smith" count the number of occurrences of "Complete" in the status column.
I've tried coding this using the SUMIF example in help topics but keep getting a #num error:
The code I entered is as follows - it is the same as the sample and I did use CTRL+SHIFT+ENTER to create it as an array:
=SUM(IF('Training Schedule'!A:A="John Smith",IF('Training Schedule'!E:E="Complete",1,0)))
What am I doing wrong?

Posted by Loren on December 11, 2000 8:32 AM

Sumif is not typed as Sum(If)
Also it is a "normal" formula, not an array.

Posted by Celia on December 11, 2000 9:18 AM

Array fomulas won't work with column refs.
Change the refs to specific range refs - for example :-

=SUM(IF('Training Schedule'!A1:A100="John Smith",IF('Training Schedule'!E1:E100="Complete",1,0)))