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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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,109,504
Messages
5,529,260
Members
409,859
Latest member
emperorgenghiskhan
Top