Convert Formula from 2010 for use in 2003

[Pulzzed]

I use the following (4) formulas in Excel 2010, but my co-worker uses the 2003 verison. How can I convert for use in excel 2003.

1) Date(Year(A11),Month(A11)+1,1
2) EOMonth(A11,0)
3) Sumifs(Sheet2!$P$3:$P$1000,Sheet2!$K$3:$k$1000,">="&A4,Sheet2!$K$3$1000,"<-"&B4)
4) Countifs(Sheet2!$e$3:$e$1000,"<>"Sheet2!$B$3:$B$1000,">="&A11,Sheet2!$B$3:$B$1000,"<="&B11)

 

[Jonmo1]

1 - will work in any version
2 - will work in any version (but must check Analysis Toolpack in Tools - Addins)
3 - That formula (as you posted it) doesn't work in XL2007
4 - That formula (as you posted it) doesn't work in XL2007

I suspect 3 & 4 can be replaced with sumproduct formulas
But please post the ACTUAL formulas as you have them WORKING in XL2007

 

[T. Valko]

I use the following (4) formulas in Excel 2010, but my co-worker uses the 2003 verison. How can I convert for use in excel 2003.

1) Date(Year(A11),Month(A11)+1,1
2) EOMonth(A11,0)
3) Sumifs(Sheet2!$P$3:$P$1000,Sheet2!$K$3:$k$1000,">="&A4,Sheet2!$K$3$1000,"<-"&B4)
4) Countifs(Sheet2!$e$3:$e$1000,"<>"Sheet2!$B$3:$B$1000,">="&A11,Sheet2!$B$3:$B$1000,"<="&B11)

In the SUMIFS formula, is that last criteria supposed to be "<="&B4?

In formula #2, the EOMONTH function is available in Excel 2003 but it has to be loaded using the Analysis ToolPak add-in.

Since not all users may have the ATP loaded you could this formula instead:

=DATE(YEAR(A11),MONTH(A11)+1,0)

Formula #3:

SUMPRODUCT(--(Sheet2!$K$3:$k$1000>=A4),--(Sheet2!$K$3:$K$1000<=B4),Sheet2!$P$3:$P$1000)

Formula #4:

Can't tell exactly what you're trying to do with that one. Can you explain it in words?

 

[Jonmo1]

I would suspect that number 3 should be

=SUMPRODUCT(--(Sheet2!$K$3:$k$1000>=A4),--(Sheet2!$K$3:$K$1000<=B4),Sheet2!$P$3:$P$1000)


But number 4 just doesn't make any sense..

 

[T. Valko]

I would suspect that number 3 should be

=SUMPRODUCT(--(Sheet2!$K$3:$k$1000>=A4),--(Sheet2!$K$3:$K$1000<=B4),Sheet2!$P$3:$P$1000)

Yeah, I missed that!

I edited my reply to correct that.

 

[Pulzzed]

The 1st 3 formulas are working great. I still need help with #4. Did you unstand what I was asking? Thanks.

 

[Jonmo1]

No we don't understand the 4th formula

Countifs(Sheet2!$e$3:$e$1000,"<>"Sheet2!$B$3:$B$1000,">="&A11,Sheet2!$B$3:$B$1000,"<="&B11)

The part in blue seems to make sense, count rows where B3:B1000 <=B11
But the part in Red does not make sense.
What is the range, and what is the criteria?

 

[Pulzzed]

Sheet 2
E column - Items that need to be counted (there are blank cells)
B column - is the date columns (items were entered on these dates)

On the report page a11 and b11 are the date range in which I need the items counted by (I trying to get monthly totals)

 

[Jonmo1]

so you need to count
Column E is NOT blank
Column B is between A11 and B11
?

Try
=SUMPRODCT(--(Sheet2!$E$3:$E$1000<>""),--(Sheet2!$B$3:$B$1000>=A11),--)Sheet2!$B$3:$B$1000,<=B11))

 

[T. Valko]

Sheet 2
E column - Items that need to be counted (there are blank cells)
B column - is the date columns (items were entered on these dates)

On the report page a11 and b11 are the date range in which I need the items counted by (I trying to get monthly totals)

Maybe this...

=SUMPRODUCT(--(Sheet2!$E$3:$E$1000<>""),--(Sheet2!$B$3:$B$1000>=A11),--(Sheet2!$B$3:$B$1000<=B11))