Problem with Ctrl Shift Enter Formula

[wilson51]

My formula is
{=SUM(IF(AND(List!$C$70:$C$208=$B4,List!G70:G208>12),List!$E$70:$E$208))}

List!$C$70:$C$208 is a range of users
List!$E$70:$E$208 is a range of amounts
List!$G$70:$G$208 is a range of numbers
$B4 is an individual user

I need the formul to do the following
For each User (B4) sum the total of values in E70:E208 where
the value in G70:G208 is greater than 12

At present the formula retuns a Nil Value.

Can you assist me?

 

[Dufus]

wilson51,
Try this. No need for ctl+shift+enter.

=SUMPRODUCT(--($C$70:$C$270=B4),--($E$70:$E$270>12),$D$70:$D$270)

Dufus

 

[wilson51]

Thanks Dufus,

Same result of Nil Value returned

 

[Dufus]

Better check and see if there are numbers in the two columns to the right of the names of if perhaps they are entered as text or something.

 

[wilson51]

Better check and see if there are numbers in the two columns to the right of the names of if perhaps they are entered as text or something.

That now returns a value but not what I'm expecting.

Lets say E70:E208 contains the following Values for a given user in B$4
250
300
275
500


And say G70:G208 contains the following
10
13
14
15

I need the formule to sum the following
300
275
500
and give me a vlue of 1075

 

[Dufus]

=SUMPRODUCT(--($C$70:$C$208=B4),--($G$70:$G$208>12),$E$70:$E$208)

I adjusted the column references. See if this works.

 

[wilson51]

=SUMPRODUCT(--($C$70:$C$208=B4),--($G$70:$G$208>12),$E$70:$E$208)

I adjusted the column references. See if this works.

Thanks Dufus,

My need was somewhat immediate and the best thing about Excel is that there is more than one way to solve a problem

John Walkenbach's "Excel Bible" is also a great reference tool.

I ended up using a dynamic crosstab array structure and it works very well.

Many thanks for your help


Garry