# Search:

Type: Posts; User: Aladin Akyurek; Keyword(s):

Page 1 of 12 1

1. ## Thread: Conditional summatioon - using SUMIF I think

Replies
2
Views
984

### X2: 13-Jun-11 Y2: ...

X2: 13-Jun-11

Y2:

=SUMIF(Date_ordered,X2,Price)

should yield a total rekated to X2.

If you enter 30-Jun-11 in X3, the formula can be copied down to obtain the total for X3.
2. ## Thread: Using the OFFSET and COUNT functions

Replies
7
Views
3,074

### Here is a b it technical explanation: ...

Here is a b it technical explanation:

3. ## Thread: LOOKUP in OpenOffice sometimes retruns error

Replies
4
Views
918

### Try... ...

Try...

=IF(C44=1;INDEX(\$A24:\$A40;MATCH(C43;C24:C40;0));"")
4. ## Thread: Lookup Text in a Range

Replies
2
Views
257

### Excel 2003 Hi Cell A10 of the attached have...

Excel 2003

Hi

Cell A10 of the attached have this formula which return correctly the text contain in a cell of the range
However, am not sure if this is a valid formula?
...
5. ## Thread: LAST free cell in row

Replies
3
Views
436

### How to have the number of last free cell in range...

How to have the number of last free cell in range A2:IV2...
In this my case 4 (D2 is the free last cell)

Doing it by means of a formula:

Control+shift+enter, not just enter...
...

Replies
5
Views
333

### Re: Lookup (2000)

Observe that I added another record to the sample you provided.

Sheet2...

B1: 30

B2: 1

B3:

Replies
5
Views
293

### Re: AutoFilter (2003)

Since you are on Excel 2003, try to exploit the Data|List|Create List functionality.
8. ## Thread: sumif two condition not works (excel)

Replies
10
Views
702

### Re: sumif two condition not works (excel)

Calling

=SUMPRODUCT((X=x)*(Y=y),Z)

'more robust' compared with:

{=SUM(IF(X=x,IF(Y=y,Z)))}

tends to have an unfortunate implication that:
9. ## Thread: sumif two condition not works (excel)

Replies
10
Views
702

### Re: sumif two condition not works (excel)

Suspected that much. But, that notion of robustness is unfortunate for it qualifies all multicondinoal formulae involving MAX, MIN, AVERAGE, MEDIAN, etc. as non-robust.
10. ## Thread: INDIRECT as part of Array formula - hows it work? (excel 97 on win xp sp2)

Replies
7
Views
640

### Re: INDIRECT as part of Array formula - hows it work? (excel 97 on win xp

Now you know how the ROW(INDIRECT(...)) bit works, isn't that a too expensive formula?

An alternative would be:

=TRIM(RIGHT(" "&A2,LEN(" "&A2)-FIND("@",SUBSTITUTE(" "&A2," ","@",LEN("...
11. ## Thread: Sum alternate cells (Excel 2000 SP3)

Replies
10
Views
2,213

### Re: Sum alternate cells (Excel 2000 SP3)

=SUMPRODUCT(--(MOD(COLUMN(B2:BB2)-COLUMN(B2)+0,2)=0),B2:BB2)
12. ## Thread: sumif two condition not works (excel)

Replies
10
Views
702

### Re: sumif two condition not works (excel)

Why is a SumProduct formula 'more robust'?
13. ## Thread: Lookup 'close' to next row (any)

Replies
14
Views
354

### Re: Lookup 'close' to next row (any)

You're right. You can reduce the formula to:

=(\$A2<>"")*(LOOKUP(\$A2+\$F\$1,GTable)<>LOOKUP(\$A2,GTable))
14. ## Thread: Lookup 'close' to next row (any)

Replies
14
Views
354

### Re: Lookup 'close' to next row (any)

Not sure I've got the story right, but try:

1) Activate Insert|Name|Define.
2) Enter GTable as name in the Names in Workbook box.
3) Enter the following formula in the Refers to box:
...
15. ## Thread: Lookup 'close' to next row (any)

Replies
14
Views
354

### Re: Lookup 'close' to next row (any)

Fred,

Just to make sure:

A2: 50, modified 50+\$F\$1=51

A3: 86, modified 86+\$F\$1=87

A4: 77, modified 77+\$F\$1=78
16. ## Thread: Lookup 'close' to next row (any)

Replies
14
Views
354

### Re: Lookup 'close' to next row (any)

=LOOKUP(MIN(A2+\$F\$1,100),{0;70;77;80;87;90},{"F";"C";"C+";"B";"B+";"A"})

where F1 a small amount for encouragement like 1 and A2 a score, suffice?
17. ## Thread: countif and AND (xl2003 sp2)

Replies
10
Views
383

### Re: countif and AND (xl2003 sp2)

Since you are on Excel 2003, select the data area (including the headers) and run Data|List|Create List. All formulas referring to (parts of) the list will adjust automatically to changes to it.

A...
18. ## Thread: COUNTIF <> '' (2002 SP3)

Replies
9
Views
414

### Re: COUNTIF '' (2002 SP3)

Hoi Hans,

That's right.

Closer to the SumProduct formula in behavior:

=COUNTA(Range)-COUNTIF(Range,"")

The error values would also be counted in by the foregoing formula.
19. ## Thread: COUNTIF <> '' (2002 SP3)

Replies
9
Views
414

### Re: COUNTIF '' (2002 SP3)

=COUNT(Range)+COUNTIF(Range,"?*")
20. ## Thread: Inserting row in sheet (2003)

Replies
19
Views
663

### Re: Inserting row in sheet (2003)

Consider exploiting the Data|List|Create List option that your Excel 2003 comes with.
21. ## Thread: Value# error (2000)

Replies
4
Views
279

### Re: Value# error (2000)

=IF(N(X25),(SUM(E25:H25)*200000)/X25,"")
22. ## Thread: Changing Sum Problem (Excel 2003)

Replies
3
Views
239

### Re: Changing Sum Problem (Excel 2003)

Since the OP is on Excel 2003, he can convert B1:D37 into a list by means of Data|List|Create List to obtain automatic formula copying and range adjustment in formulas that reference the list area.
...
23. ## Thread: formula for count (2003)

Replies
6
Views
292

### Re: formula for count (2003)

Given:

2,3
2,4
4,2
2,3
2,blank

would the result be 4?
24. ## Thread: formula for count (2003)

Replies
6
Views
292

### Re: formula for count (2003)

For excluding blanks, the idiom would be:

=SUM(IF(A1:A200<>"",1/COUNTIF(A1:A200,A1:A200)))

followed by control+shift+enter.

Replies
3
Views
206

### Re: countif (excel2003)

=COUNTIF(Range,"*(RM)*")
Results 1 to 25 of 283
Page 1 of 12 1