Match Count IF formula

Killer17

Active Member
Joined
Jun 30, 2007
Messages
258
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
 

Some videos you may like

Excel Facts

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

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
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
Joined
Jun 30, 2007
Messages
258
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
Joined
Sep 3, 2008
Messages
652
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
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top