Sumifs and Index Match returning 0% for blanks

[AndrewD04]

HI,

Thank you for taking the time to look at my question.

I am trying to write a formula that will return a value based on some criteria however some the the data has blank cells with no dat in so i want it to ignore these and leave them blank in the formula returning cell.

What i am trying to do is if names match on both sheets and Dates match return Column F or J (Formula depending column F and J are the same)

ive tried to be descriptive as possible as i cant load a mini excel up it just wont allow me

I have tried it 2 ways and both formulas are treating the blank cells as a 0

=INDEX(Sheet5!F:F,MATCH([@[Person Name]],Sheet5!B:B,MATCH([@WC],Sheet5!A:A))) and =SUMIFS(Sheet5!J:J,Sheet5!B:B,@'Data'!B:B,'Data'!A:A,@Sheet5!A:A)

Sum if formula :
Sheet 5 J:J is a % value (Sometimes blank)
Sheet 5 B:B is a Person name
Data B:B is Person name
Data A:A is a date
Sheet 5 A:A is a date

Index Formula:
Sheet 5 F:F is a % value (Sometimes blank)
Sheet 5 B:B Person Name
Sheet 5 A:A Date

i did try adding "<>" to the sum if but was not sure how to do it.

 

[zero269]

HI,

Thank you for taking the time to look at my question.

I am trying to write a formula that will return a value based on some criteria however some the the data has blank cells with no dat in so i want it to ignore these and leave them blank in the formula returning cell.

What i am trying to do is if names match on both sheets and Dates match return Column F or J (Formula depending column F and J are the same)

ive tried to be descriptive as possible as i cant load a mini excel up it just wont allow me

I have tried it 2 ways and both formulas are treating the blank cells as a 0

=INDEX(Sheet5!F:F,MATCH([@[Person Name]],Sheet5!B:B,MATCH([@WC],Sheet5!A:A))) and =SUMIFS(Sheet5!J:J,Sheet5!B:B,@'Data'!B:B,'Data'!A:A,@Sheet5!A:A)

Sum if formula :
Sheet 5 J:J is a % value (Sometimes blank)
Sheet 5 B:B is a Person name
Data B:B is Person name
Data A:A is a date
Sheet 5 A:A is a date

Index Formula:
Sheet 5 F:F is a % value (Sometimes blank)
Sheet 5 B:B Person Name
Sheet 5 A:A Date

i did try adding "<>" to the sum if but was not sure how to do it.

See if this is what you're looking for:

How to Calculate Only If Cell is Not Blank in Excel

How to do operation only if cell is not blank? First check if excel cell not empty, then use the true to calculate and false to do nothing. See these example of calculating only if no cell is blank.

www.exceltip.com

That article shows a few different methods for only calculating cells that are NOT empty.

 

[Fluff]

How about

=SUMIFS(Sheet5!J:J,Sheet5!B:B,[@[Person Name]],Sheet5!A:A,[@WC])