=SUMIF Help!!
 

Any one can help up the following case, how to set up the number in
"criteria" to be continue, so that i not neet to key in one by one, sample

=SUMIF('Data In (Agent 1)'!B14:B233,"003",'Data In (Agent 1)'!C14:C233)

as above sample format, how to automatically adjust the number "003" to
"004" for the next row format?

thank you.

regards,
Lee

=SUMIF Help!!
 

Use Row(A3) instead of 3 then use TEXT to make it a string

=SUMIF('Data In (Agent 1)'!B14:B233,TEXT(ROW(A3),"#000"),'Data In (Agent
1)'!C14:C233)


=SUMIF('Data In (Agent 1)'!B14:B233,"003",'Data In (Agent 1)'!C14:C233)


" wrote:

Any one can help up the following case, how to set up the number in
"criteria" to be continue, so that i not neet to key in one by one, sample

=SUMIF('Data In (Agent 1)'!B14:B233,"003",'Data In (Agent 1)'!C14:C233)

as above sample format, how to automatically adjust the number "003" to
"004" for the next row format?

thank you.

regards,
Lee

=SUMIF Help!!
 

We can use the ROWS() function to generate the last digit. Say in A1 thru
B30 we have:

004 107
009 104
007 117
009 100
009 111
002 108
004 116
001 109
002 101
002 114
007 113
003 102
003 117
007 103
002 108
004 105
004 110
006 118
006 103
008 120
001 118
002 111
006 105
009 120
001 119
002 106
008 109
005 104
003 104
004 101

were column A is Text

Instead of a set of formulas like:

=SUMIF(A1:A30,"001",B1:B30)
=SUMIF(A1:A30,"002",B1:B30)

we can use:

=SUMIF($A$1:$A$30,"00" & ROWS($A$1:A1),$B$1:$B$30) and copy down
--
Gary''s Student - gsnu200856


" wrote:

Any one can help up the following case, how to set up the number in
"criteria" to be continue, so that i not neet to key in one by one, sample

=SUMIF('Data In (Agent 1)'!B14:B233,"003",'Data In (Agent 1)'!C14:C233)

as above sample format, how to automatically adjust the number "003" to
"004" for the next row format?

thank you.

regards,
Lee