# Problem with Ctrl Shift Enter Formula

#### wilson51

##### Board Regular
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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

Thanks Dufus,

Same result of Nil Value returned

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.

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

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.

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.

Garry

Replies
3
Views
105
Replies
6
Views
766
Replies
0
Views
372
Replies
0
Views
51
Replies
9
Views
741

1,219,579
Messages
6,149,102
Members
450,860
Latest member
Kooth

### 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.

### Which adblocker are you using?

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

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