use of "if" based on text in another column

G

Gary D.

Is there a way to use an 'if' statement in a column that is using an "=b1*c1"
statement to not preform the function if the text in column A starts with a
"Q"
We have a sheet that the first column has job #'s that start with an "X" and
Quote #'s that start with a "Q", I don't want the price of the quote numbers
to be included in the total for the column.


Thanks

Gary D.
 
G

goober

=IF(LEFT(A1,1)="Q","",B1*C1)

I think this will do what you are asking. Hope it helps.
 
R

Ron Coderre

If you want the column total to only include values where Col_B begins with
an "X", try something like this:

=SUMIF(B1:B10,"X*",C1:C10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Rikki Ward

Hi Ron, or anyone else

I am trying to do a similar exercise, but drill down through a number of
worksheets. I want to only sum those values which appear on a worksheet
where A1 has the value "stat". aprstat and marstat are sheetnames at the
start and end of the ranges to be summed

I have found

=SUM(IF((aprstat:marstat!A1="stat"),aprstat:marstat!B6)) gives #name?,
and
=SUMIF(aprstat:marstat!A1,"=stat",aprstat:marstat!B6) gives #value!.

I can get the equivalent sum command to work across the sheets
=SUM(aprstat:marstat!B6) .

Can anyone help?

Rikki

--

If you want the column total to only include values where Col_B begins with
an "X", try something like this:

=SUMIF(B1:B10,"X*",C1:C10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
P

Peo Sjoblom

It's not possible to use the same method when using sumif across multiple
sheets.

1. you need to put the names of ALL sheets that you want to be included, not
just the first and the last, so if you have for instance 6 sheets you have
to put all 6 names in a range like H1:H6 or something then use that range in
your formula as follows

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H6&"'!$A$1"),"stat",INDIRECT("'"&H1:H6&"'!
$B$6")))


so put your sheet names in a range and replace H1:H6 with that range


--

Regards,

Peo Sjoblom
 
R

Rikki Ward

Thanks Peo

I'll have a play with this tomorrow.

Have a good Christmas break

Rikki

--

It's not possible to use the same method when using sumif across multiple
sheets.

1. you need to put the names of ALL sheets that you want to be included, not
just the first and the last, so if you have for instance 6 sheets you have
to put all 6 names in a range like H1:H6 or something then use that range in
your formula as follows

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H6&"'!$A$1"),"stat",INDIRECT("'"&H1:H6&"'!
$B$6")))


so put your sheet names in a range and replace H1:H6 with that range


--

Regards,

Peo Sjoblom
 
Top