Help on a formula

[kingofhearts]

I need help on a formula as below:
{=SUM(1*('Site Management'!$E$8:$E$287=H$5)*('Site Management'!$AA$8:$AA$287<>""))}
resulted as:


[TABLE="width: 508"]
<colgroup><col><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Description:[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Region-1[/TD]
[TD]Region-2[/TD]
[TD]Region-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Visit in Plan[/TD]
[TD] [/TD]
[TD]135[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]43[/TD]
[TD]33[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

You can use a SUMPRODUCT formula instead of the one you posted which does not require CTRL-SHIFT-ENTER.
=SUMPRODUCT(--('Site Management'!$E$8:$E$287=H$5),--('Site Management'!$AA$8:$AA$287<>""))

But how do you want to get those numbers exactly? Please provide more details.

 

[kingofhearts]

You can use a SUMPRODUCT formula instead of the one you posted which does not require CTRL-SHIFT-ENTER.
=SUMPRODUCT(--('Site Management'!$E$8:$E$287=H$5),--('Site Management'!$AA$8:$AA$287<>""))

But how do you want to get those numbers exactly? Please provide more details.

Hi Scott Thanks for your reply let me explain you the detail:
i have one main sheet which contains dates to every milestone against one id like Visit in plan is one coulmn heading and in rows i have site ids, and the data in visit in plan is in dates so i need to calculate nonblank cells against a critaria in another coulmn as region.
___________________________________________________________________________
Site id + Region + Visit in Plan + Visit Done + Work Start + Work Done + Handedover
site1 + Region 1 + 13sep13 + 14sep13 + 16sep13 + 19sep13+ 21sep13
and my summary wold contains:
Milestone Region1 Region2 Region3
Visit in plan 4 3 5
Visit Done 3 2 3
Work Start 2 2 3
Work Done 2 0 3
Handedover 1 0 2

It help me to easily view the delta. The formula was told to me by some of my friend and he applied the same on my sheet and it works really fine.

Really appriciated if you please help me to understand this.