SUMIFS criteria with blanks

[Nakkivaan]

Hello,

I try to calculate total amounts with SUMIFS. Sheet Products contains name of products, category of products, release and end date of products and amount information on rows. Sheet Amounts is used to calculate total amounts based on categories.

I have couple different criteria and one is related to dates. The problem is in the last criterion of function. Products!E:E contains release dates of products. Cell O2 is one month for I like to calculate total amount of active products (if product is not released yet, it will not be included in total).

This works fine, but I have problem if cell is blank in Products-sheet. I want to include blank cells also to total, because I don't have every start date for products - but amounts I do. I have tried different kind of IF-functions, but not with success.

So everything in short: how I can adjust last criteria, so that function sums column E cell if it is blank (current criteria should be in use also - I don't want to add any invalid dates)?

=SUMIFS(Products!$Q:$Q;Products!A:A;'Amounts'!A1;Products!E:E;"<=" & 'Amounts'!$O$2)

Best regards
JV

 

[FormR]

I want to include blank cells also to total, because I don't have every start date for products - but amounts I do.

Hi, as long as the blank cells are truly blank then here is one option you can try.

=SUMPRODUCT(SUMIFS(Products!$Q:$Q,Products!A:A,amounts!A1,Products!E:E,CHOOSE({1,2},"<=" & amounts!$O$2,"")))

 

[Nakkivaan]

This worked, thank you.

However I'm not able follow this exactly. Can you open it?

-JV

 

[FormR]

This worked, thank you.

Great, good to hear!

I'm not able follow this exactly. Can you open it?

It's effectively doing two SUMIFS(), one with the less than and equal to criteria and one with the blank criteria and summing result of both.