sumproduct exclude header

[ZedaG]

Hello All,

I've searched for a solution but I can't make the answers to similar threads work for me.

I want to use sumproduct on date columns (and sometimes date and numerical columns and/or text) and I want the formula to treat the data in those columns as a dynamic range (with constantly changing outer ranges), but I have headers and they have to stay. The data is not in a table format and there are no named ranges. The reason for this is we merge several workbooks periodically and the named ranges, etc. mess up the merging.

The formula I use is this:

=SUMPRODUCT((MONTH(ProductMaster!$A$2:$A$250)=2)*(YEAR(ProductMaster!$A$2:$A$250)=2017)*((ProductMaster!$A$2:$A$250)>3))

and I want the ProductMaster!$A$2:$A$250 to be treated as a dynamic range to exclude headers.

Is this even possible?

Thanks

Zeda

[AliGW]

Where are the headers?

[ZedaG]

Sorry,

Column Labels

Date Sites
1/1/2017 3

Zeda

[Jonmo1]

The big issue is when that formula comes across a text string (A header), then it returns #Value! error, right?
I suggest using a different formula (like countifs) that won't error in that case...

Try
=COUNTIFS(ProductMaster!$A$2:$A$250,">="&DATE(2017,2,1),ProductMaster!$A$2:$A$250,"<"&DATE(2017,3,1))

This will not error when a text value is in column A.

That 3rd criteria (A2:A250 > 3) seems redundant, so I removed it.

[ZedaG]

Sorry my bad again= really having one of those mornings.

=COUNTIFS(ProductMaster!$H:$H,">="&DATE(2017,2,1),ProductMaster!$H:$H,"<"&DATE(2017,3,1)) works. But the *((ProductMaster!$A$2:$A$250)>3)) should have been *((ProductMaster!$AD$2:$AD$250)>3)).

Zeda

[Jonmo1]

Ok, 3rd criteria added

=COUNTIFS(ProductMaster!$A$2:$A$250,">="&DATE(2017,2,1),ProductMaster!$A$2:$A$250,"<"&DATE(2017,3,1),ProductMaster!$AD$2:$AD$250,">3")

[ZedaG]

Jonmo1,

Thank you - that works slick.

I really appreciate you're help.

[Jonmo1]

You're welcome.