Problem with Ctrl Shift Enter Formula

wilson51

Board Regular
Joined
Mar 31, 2003
Messages
50
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
wilson51,
Try this. No need for ctl+shift+enter.
Code:
=SUMPRODUCT(--($C$70:$C$270=B4),--($E$70:$E$270>12),$D$70:$D$270)
Dufus
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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

Board Regular
Joined
Mar 31, 2003
Messages
50

ADVERTISEMENT

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

Board Regular
Joined
Aug 19, 2006
Messages
176
Code:
=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

Board Regular
Joined
Mar 31, 2003
Messages
50
Code:
=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
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top