# Match Count IF formula

#### Killer17

##### Active Member
Hey all I using this formula in

Cell C8

=SUMIF('[Statistics.xlsx]Male Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Male Stats'!\$D\$5:\$D\$9999)+SUMIF('[Statistics.xlsx]Female Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Female Stats'!\$D\$5:\$D\$9999)

I want to update it look into cell B8 and if cell B8 = ABS or DUM to return a zero in cell C8
If B8 is blank then return a number from the formula above.

Thanks

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### pagrender

##### Well-known Member
Try this:

=IF(OR(B8="ABS",B8="DUM"),0,IF(ISBLANK(B8),SUMIF('[Statistics.xlsx]Male Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Male Stats'!\$D\$5:\$D\$9999)+SUMIF('[Statistics.xlsx]Female Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Female Stats'!\$D\$5:\$D\$9999)))

#### Killer17

##### Active Member
Thanks pagrender....

#### Killer17

##### Active Member
Another option I need added to this formual that works great.

If Cell B8 = DEC or blank leave the amount.

=IF(OR(B8="ABS",B8="DUM"),0,IF(ISBLANK(B8),SUMIF('[Statistics.xlsx]Male Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Male Stats'!\$D\$5:\$D\$9999)+SUMIF('[Statistics.xlsx]Female Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Female Stats'!\$D\$5:\$D\$9999)))

Thanks again

#### pagrender

##### Well-known Member
Hi again,

Just to make sure this is what you're asking for:

If B8 equals "ABS" or "DUM", you would like a zero in C8
If B8 equals "DEC" or blank, use the results of the formula you originally provided.

If that's the case, try this formula:
=IF(OR(B8="ABS",B8="DUM"),0,IF(OR(B8="DEC",ISBLANK(B8)),SUMIF('[Statistics.xlsx]Male Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Male Stats'!\$D\$5:\$D\$9999)+SUMIF('[Statistics.xlsx]Female Stats'!B\$5:B\$9999,\$A8,'[Statistics.xlsx]Female Stats'!\$D\$5:\$D\$9999)))

Hope this helps,
Pete

Replies
9
Views
75
Replies
3
Views
30
Replies
3
Views
51
Replies
3
Views
62
Replies
4
Views
172

1,108,843
Messages
5,525,179
Members
409,629
Latest member
McGuilliam