Multiple criteria one range using SUMIFS, EOMONTH and blank cells

[jayped]

Hi, I am seeking help on a formula which allows me to sum a column based on multiple criteria. The formula I am currently using is as follows:

SUM(SUMIFS(Data!F:F,Data!J:J,">"&EOMONTH($B$5,-1),Data!A:A,"<"&EOMONTH($B$5,-1)+1,Data!$B:$B,"W"))

The first criteria (in bold) is the portion I am having the most difficulty with. I would like to perform a sum of data in column F in the Data tab based on two criteria for the dates in Column J in the Data tab; 1) if the date is greater than the last day of the previous month (hence eomonth function) and 2) if any cells are blank.

I have tried using curly brackets {">"&EOMONTH(B5,-1),""} but to no success. I cannot find anything that helps.



Regards

 

[Fluff]

Based on your description, how about
=SUMIFS(Data!F:F,Data!J:J,">"&EOMONTH(B5,-1))

 

[jayped]

What if a date hasn't been entered and the cell is left blank. I'd like the corresponding F row to be included in the sum total as well.

 

[Fluff]

In that case try
=SUMPRODUCT((Data!J3:J19>EOMONTH(B5,-1))+(Data!J3:J19=""),Data!F3:F19)

 

[jayped]

Thank you. It has worked so far. However, I need another criteria in the formula - that is Data!B3:19="W". How can I incorporate this into the formula? What I have below isn't working. It's including the amount where DataJ3:J19="" whether there is a W or not in column B.

SUMPRODUCT((Data!$B$5:$B$212="W")*(Data!$A$5:$A$212<eomonth($b$5,-1))*(data!$j$5:$j$212>EOMONTH($B$5,-1))+(Data!$J$5:$J$212=""),Data!$F$5:$F$212)

Thanks.

 

[Fluff]

How about
=SUMPRODUCT((Data!$B$5:$B$212="W")*(Data!$A$5:$A$212< EOMONTH($B$5,-1))*((Data!$J$5:$J$212>EOMONTH($B$5,-1))+(Data!$J$5:$J$212="")),Data!$F$5:$F$212)

 

[jayped]

Perfect. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback