condition adding

**choy96** · 03-21-2010, 01:22 AM

i have a list below:

AD123FGAC----1
AB123FGAC----2
AG123FBAC----1
AD123FEAC----1
AB123FEAC----1

I need a formula can be total the quantity according FG , FB, FE(word after the number only)
AG123FBAC may confuse is FB or FG beacause having G on the front.

thanks you.

**JBeaucaire** · 03-21-2010, 02:13 AM

Put this in C10, then copy down:

=SUMIF($B$2:$B$6, "*" & B10 & "*", $C$2:$C$6)

**choy96** · 03-21-2010, 07:34 AM

thanks JBeaucaire. The result i have get is confuse already because FG get result quantity is 3, the correct is 2 only.
Does have formula will check character number 6 and 7? the part number will always be same 9 digit .

please help.

SORRY, RESULT 3 IS CORRECT. SORRY TO ALL OF U.

**JBeaucaire** · 03-21-2010, 07:36 AM

The sum of the FG quantities is 3, not 2.

**choy96** · 03-21-2010, 07:47 AM

sorry, if i explain wrongly. i need the formula to check the digit no.6 and no.7 only. The other digit no. will not check.
AG123FBAC
A-digit no.1
G-digit no.2
1-digit no.3
2-digit no.4
3-digit no.5
F-digit no.6
B-digit no.7
A-digit no.8
C-digit no.9

**DonkeyOte** · 03-21-2010, 08:03 AM

If needed you can make more specific re: char position with use of ? wildcard, ie:

C10: =SUMIF($B$2:$B$7,"?????"&$B10&"*",$C$2:$C$7)

but based on your sample JB's prior suggestion works - the sum of quantity for "FG" is most definitely 3 (the frequency would be 2).

**choy96** · 03-24-2010, 03:04 AM

i have the formula to subtotal individual FG, FB, FE. can i have another formula that subtotal the quantity is not FG and FE? because got many part number such as FC, FV ...but got 2 type only, FG and FE consider type 1 , other will be type 2.

please help

**DonkeyOte** · 03-24-2010, 07:47 AM

It's unclear which of the suggestions you're using but (without knowing all of the particulars) one route might be:

=SUM(SUMIF($B$2:$B$7,{"<>??????FG*","<>??????FE*"},$C$2:$C$7))

Or alternatively simply calculate the SUM of C2:C7 and subtract from that the results for FE & FG which you've already calculated.