Multiple SUMIFS based on criteria but ignores if a cell is blank..

**Andrewbutler** · 11-22-2014, 06:00 AM

Hi All,

Sorry if the subject is a big vague but thought I would put all the required information in the description, Hope there are some clever heads out there who can assist me

I have a SUMIFS:

=SUMIFS('Working Copy'!AK:AK,'Working Copy'!W:W,'Summary Filter'!F6,'Working Copy'!F:F,">"&C6,'Working Copy'!Z:Z,'Summary Filter'!C4)

Basically it looks at the Raw data called working copy and pulls through revenues based on a supplier but also looks at the input based on a few other cells.

What I need is to wrap an IF around the SUMIFS to look at these criteria but I need it to look at two main cells if one is blank the look at the other and vice versa, also if both cells are blank then display results without this criteria like also if there are data in both then ignore again.

Hope this makes sense? I have attached an image of the spread sheet which may makes things more clearer. Let me know if there is anything else which you need from me.

Many thanks,

Andrew

**Glenn Kennedy** · 11-22-2014, 06:18 AM

Please attach the spreadsheet, not an image. At least on my PC - the image isn't visible.

**Andrewbutler** · 11-22-2014, 06:35 AM

Please see attached document. The cells in Orange are the ones in question.

Thanks,

Andrew

**ELeGault** · 11-22-2014, 06:51 AM

Without opening your Zip file...sounds like you are stating the following.

Check if Cell 1 is empty,

1TRUE= Check if Cell 2 is BLANK or Empty,

2TRUE= If it is also than result is BLANK

2FALSE= If Cell 2 is NOT BLANK where Cell 1 is BLANK then process formula based on cell 2

1FALSE=if 1 is NOT BLANK, check again to see if Cell 2 is BLANK

3TRUE=if CELL 2 is than your formula based on Cell 1 not BLANK and While cell 2 is BLANK

3False=Otherwise neither are blank so process formula based on both NOT BLANK

=IF(OR(CELL 1="",CELL 1=0),IF(OR(CELL 2="",CELL 2=0),"", YOUR FORMULA WITH CELL 2 not blank),IF(OR(CELL 2="",CELL 2=0),FORMULA When CELL 1 IS NOT BLANK but CELL 2 IS),Formula when Neither cell is blank)

**Andrewbutler** · 11-22-2014, 07:21 AM

Thanks for the response, appreciate the formula you put in. Having a little trouble incorporating it directly in my current SUMIFS though.. I am not the most advanced Excel user and doing this SUMIFS was quite big for me so sorry for sounding quite amateur

**ELeGault** · 11-22-2014, 07:56 AM

Always glad to help - Paste this below "Andrew" in CELL F7

=IF(OR($C$4=0,$C$4=""),IF(OR($C$6=0,$C$6=""),"",SUMIFS('Working Copy'!$AK:$AK,'Working Copy'!$W:$W,F$6,'Working Copy'!$F:$F,">"&$C$6)),IF(OR($C$6=0,$C$6=""),SUMIFS('Working Copy'!$AK:$AK,'Working Copy'!$W:$W,F$6,'Working Copy'!$Z:$Z,$C$4),SUMIFS('Working Copy'!$AK:$AK,'Working Copy'!$W:$W,F$6,'Working Copy'!$F:$F,">"&$C$6,'Working Copy'!$Z:$Z,$C$4)))

Then copy paste the cell over under Butler.

**Glenn Kennedy** · 11-22-2014, 08:49 AM

A small amendment to ElEGault's solution (to take account of the situation when BOTH C4 & c6 are empty). The formula can be dragged over to the right as far as is needed to cope with all your suppliers/staff - whatever they are..

**ELeGault** · 11-22-2014, 08:52 AM

When both were blank I left blank (thought that was the desired outcome, but right on, hope that closes it out then!

Thanks Bed time... 6 AM...

**Glenn Kennedy** · 11-22-2014, 09:06 AM

There's dedication for you... 6 a.m. and posting away furiously. Welcome to the forum...

**Andrewbutler** · 11-22-2014, 10:45 AM

I will give this a whirl, looks good to me! Appreciate both of your work on this, really will help me

If ok will keep this thread open for a little bit just whilst I put the real data back in and make sure I don't mess it up somehow!

**Glenn Kennedy** · 11-22-2014, 10:54 AM

OK. No problem. I'll be here (off and on) for a few more hours today. Fruit canes to clip back for a while now...

**Andrewbutler** · 11-23-2014, 10:55 AM

There is one more permutation which I need to add in but now the formula is very long (as long as it works though it is all cool) I have added a new column at the end which is a Y or N, where the cell shows Y, I need to exclude that value in the overall total.

How can I add in this to the formula?

**Glenn Kennedy** · 11-23-2014, 11:02 AM

Im on a plane! I'll be back in about two hours.

**Glenn Kennedy** · 11-23-2014, 11:03 AM

Can you post the sheet, complete with the additional column?

**Andrewbutler** · 11-23-2014, 11:14 AM

Please see attached, I have highlighted the Y or N in the raw data, so where it is Y I need the revenue excluded

**Glenn Kennedy** · 11-23-2014, 02:31 PM

You caused a bit of confusion there. You attached a version of your sheet that didn't have the last version of the formulas. It's fixed now. Take a look and let me know....

**Andrewbutler** · 11-23-2014, 03:04 PM

Hope you had a safe journey by the way

**Glenn Kennedy** · 11-23-2014, 03:26 PM

Yep. Soon to be the sad "table for one, please" in central London... Beer time first, though.

**Andrewbutler** · 11-23-2014, 06:30 PM

Enjoy! And thanks alot for all the help, really appreciate it, worked like a dream

Multiple SUMIFS based on criteria but ignores if a cell is blank..

LinkBack

Thread Tools

Rate This Thread

Display

Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Re: Multiple SUMIFS based on criteria but ignores if a cell is blank..

Thread Information

Users Browsing this Thread

Similar Threads

SUMIFS: If no criteria match, leave cell blank, else return value

[SOLVED] Nested IF and SUMIFS - Multiple criteria based on an IF

SUMIFS with 1 criteria range, multiple criteria with cell references

to change a descending rank formula that ignores '0's to one that ignores blank cells

How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

Bookmarks

Bookmarks

Posting Permissions