Need help with useing Sumproduct with named range and date funtion

[fgruhlke]

Hello there! Hope you can help me....

I want to be able to find the total miles traveled for a group of employees during a particular date range. I like the use of the "Month" function for ease of use in up dateing the results on the fly.

The group of employees would be stored in a named range.

A16:A38 has the list of Employees.
B16:B38 has the Dates.
Q16:Q38 has the Miles.
E7 is the date range I want to sum.
E8 is the employee or group of employees I want to sum.


This formula gives me the sum of miles for a date range for a single employee but wont work with a named range for a group of employees.

=SUMPRODUCT((A16:A38=E8)*(MONTH(B16:B38)=E7),Q16:Q38)

This formula give me the sum of miles for a whole group of employees but I cant figure out how to add a date range.

=SUMPRODUCT(SUMIFS(Q16:Q38,A16:A38,INDIRECT(E3)))


Can you suggest a way to combine these 2 formulas or perhaps something similar that would meet the desired results?

Thank you in advance.

[XOR LX]

Hi,

Assuming your Named Range for employees is called Employees, your first formula would be:

=SUMPRODUCT((ISNUMBER(MATCH(A16:A38,Employees,0)))*(MONTH(B16:B38)=E7)*Q16:Q38)

Regards

[fgruhlke]

I am getting a #VALUE error, sorry for not being more clear, the employee # are alphanumeric ie: "60TE50"

[Pepe Le Mokko]

What is E7 actually? A number from 1 to 12 ?

[XOR LX]

It shouldn't matter that they're alphanumeric.

I think you you should upload a workbook in order for us to be able to determine the issue. Obviously replace any confidential/sensitive information with dummy data if necessary.

Regards

[fgruhlke]

E7 = various dates....

Call Date
1/2/2014
2/2/2014
2/2/2014
1/2/2014
3/3/2014
1/3/2014
1/3/2014

[Pepe Le Mokko]

In this cae perhaps XOR's formula should be amended a bit

Please Login or Register  to view this content.

( without seeing the data on a sample sheet it's a wild guess)

[XOR LX]

Agreed, Pepe. Wild guess. Though am also a bit confused that, if E7 contains an actual date (and not simply a number from 1 to 12), then if, as the OP said:

"This formula gives me the sum of miles for a date range for a single employee but wont work with a named range for a group of employees"

how did the formula work in the first place?

Regards

[fgruhlke]

=SUMPRODUCT((ISNUMBER(MATCH(A16:A38,Employees,0)))*(MONTH(B16:B38)=MONTH(E7)*Q16:Q38)

I get a #value error with this as well...

I did upload the workbook were I am gathering the various sample formulas I will need to build various dashboard style reports.


Thanks for all the help so far!

[martindwilson]

your range q16:q38 has spaces instead of blanks in some of the cells you cannot multiply a space thats why you get value errors

[XOR LX]

What precise range did you use for the Named Range Employees when you tried my formula?

Regards

Edit: well spotted, Martin!

[fgruhlke]

XOR LX, I appoligize for my confusion... E7 IS a number 1-12

[fgruhlke]

From the work book I uploaded..

=SUMPRODUCT((ISNUMBER(MATCH(A16:A38,Team1,0)))*(MONTH(B16:B38)=E7)*Q16:Q38)

[fgruhlke]

This works as long as there are no blank cells in Q16:Q38

=SUMPRODUCT((ISNUMBER(MATCH(A16:A38,Team1,0)))*(MONTH(B16:B38)=E7)*Q16:Q38)

I can work with this solution... Thank you guys for you help and again, I appoligize for the confusion I created!

[Tony Valko]

This works as long as there are no blank cells in Q16:Q38

=SUMPRODUCT((ISNUMBER(MATCH(A16:A38,Team1,0)))*(MONTH(B16:B38)=E7)*Q16:Q38)

If you use the comma argument separator syntax it will ignore any text entries in the sum range:

=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,Team1,0)),--(MONTH(B16:B38)=E7),Q16:Q38)

[fgruhlke]

Excellent work Tony, thank you for the tip!! Works great!

[Tony Valko]

You're welcome. Thanks for the feedback!