Search:

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.

Here is a b it technical explanation:

http://www.mrexcel.com/forum/showthread.php?t=310278

Try...

=IF(C44=1;INDEX($A24:$A40;MATCH(C43;C24:C40;0));"")

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

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

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

Sheet2...

B1: 30

B2: 1

B3:

Since you are on Excel 2003, try to exploit the Data|List|Create List functionality.

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:

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.

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("...

=SUMPRODUCT(--(MOD(COLUMN(B2:BB2)-COLUMN(B2)+0,2)=0),B2:BB2)

Why is a SumProduct formula 'more robust'?

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

=($A2<>"")*(LOOKUP($A2+$F$1,GTable)<>LOOKUP($A2,GTable))

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

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

Given your intention, wouldn't

=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?

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

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.

=COUNT(Range)+COUNTIF(Range,"?*")

Consider exploiting the Data|List|Create List option that your Excel 2003 comes with.

=IF(N(X25),(SUM(E25:H25)*200000)/X25,"")

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

Given:

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

would the result be 4?

For excluding blanks, the idiom would be:

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

followed by control+shift+enter.

=COUNTIF(Range,"*(RM)*")