array formula not working

[Scruff57]

I am trying to set up an array formula to calculate data based on employee id. This same formula works with similar data on another worksheet, but for some reason I can't get it to work with this other worksheet. I know I am missing something stupid, perhaps someone can show me my error.

Worksheet1 - raw data
1 A B C D
2 Date center emplID total1

named ranges include
bbtAgentID = (C3:C20000)
bbtUseAppr = (D320000)
bbtUseDate = (A3:A20000)

The results should be based on the following parameters
Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7
results should be the sum of all the numbers in col D for that agentID between the dates listed in J7 and J8

the formula I am trying to get to work is
{=SUMPRODUCT(IF(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr)))}

I get no results with this formula, but I can if I filter the data on the worksheet. The data falls within the range of the name ranges. I use this same formula on another worksheet, changing the names to the appropriate ones and it works, What am I doing wrong here?

 

[Kevin Stecyk]

Scruff57

Try this:

{=SUMPRODUCT(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate)*(bbtUseAppr
))}

Untested.

You don't believe you need your IF statement. Your sumproduct is doing that
for you.

Best regards,
Kevin
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I am trying to set up an array formula to calculate data based on employee
id. This same formula works with similar data on another worksheet, but for
some reason I can't get it to work with this other worksheet. I know I am
missing something stupid, perhaps someone can show me my error.

Worksheet1 - raw data
1 A B C D
2 Date center emplID total1

named ranges include
bbtAgentID = (C3:C20000)
bbtUseAppr = (D320000)
bbtUseDate = (A3:A20000)

The results should be based on the following parameters
Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7
results should be the sum of all the numbers in col D for that agentID
between the dates listed in J7 and J8

the formula I am trying to get to work is
{=SUMPRODUCT(IF(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseA
ppr)))}

I get no results with this formula, but I can if I filter the data on the
worksheet. The data falls within the range of the name ranges. I use this
same formula on another worksheet, changing the names to the appropriate
ones and it works, What am I doing wrong here?

 

[Mark Graesser]

Scruff,
You could also change the formula to:

=SUMPRODUCT(($B$1=bbtAgentID)*(J8>=bbtUseDate)*(J7<=bbtUseDate)*(bbtUseAppr))

and avoid the array entry.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Scruff57 wrote: -----

I am trying to set up an array formula to calculate data based on employee id. This same formula works with similar data on another worksheet, but for some reason I can't get it to work with this other worksheet. I know I am missing something stupid, perhaps someone can show me my error.

Worksheet1 - raw data
1 A B C D
2 Date center emplID total1

named ranges include
bbtAgentID = (C3:C20000)
bbtUseAppr = (D320000)
bbtUseDate = (A3:A20000)

The results should be based on the following parameters
Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7
results should be the sum of all the numbers in col D for that agentID between the dates listed in J7 and J8

the formula I am trying to get to work is
{=SUMPRODUCT(IF(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr)))}

I get no results with this formula, but I can if I filter the data on the worksheet. The data falls within the range of the name ranges. I use this same formula on another worksheet, changing the names to the appropriate ones and it works, What am I doing wrong here?

 

[Mark Graesser]

Scruff
It looks like your inequalities are backwards. Your formula says the use date must be before the start date and after the end date

Good Luck
Mark Graesse
(e-mail address removed)

----- Scruff57 wrote: ----

I am trying to set up an array formula to calculate data based on employee id. This same formula works with similar data on another worksheet, but for some reason I can't get it to work with this other worksheet. I know I am missing something stupid, perhaps someone can show me my error

Worksheet1 - raw dat
1 A B C D
2 Date center emplID total1

named ranges includ
bbtAgentID = (C3:C20000
bbtUseAppr = (D320000
bbtUseDate = (A3:A20000

The results should be based on the following parameter
Agent ID as listed in cell B
Date range starting - J
Date range ending - J
results should be the sum of all the numbers in col D for that agentID between the dates listed in J7 and J

the formula I am trying to get to work i
{=SUMPRODUCT(IF(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr)))

I get no results with this formula, but I can if I filter the data on the worksheet. The data falls within the range of the name ranges. I use this same formula on another worksheet, changing the names to the appropriate ones and it works, What am I doing wrong here

 

[Roger Govier]

Hi Scruff

Try
=SUMPRODUCT(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr))

not as an array formula.

--
Regards
Roger Govier
I am trying to set up an array formula to calculate data based on employee id. This same formula works with similar data on another worksheet, but for some reason I can't get it to work with this other worksheet. I know I am missing something stupid, perhaps someone can show me my error.

Worksheet1 - raw data
1 A B C D
2 Date center emplID total1

named ranges include
bbtAgentID = (C3:C20000)
bbtUseAppr = (D320000)
bbtUseDate = (A3:A20000)

The results should be based on the following parameters
Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7
results should be the sum of all the numbers in col D for that agentID between the dates listed in J7 and J8

the formula I am trying to get to work is
{=SUMPRODUCT(IF(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr)))}

I get no results with this formula, but I can if I filter the data on the worksheet. The data falls within the range of the name ranges. I use this same formula on another worksheet, changing the names to the appropriate ones and it works, What am I doing wrong here?

 

[Kevin Stecyk]

Mark,

Yes, you are correct with regard to the arrays entered formula.

The greater and less than symbols I am less sure about.

The data falls within the range of the name ranges.

Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7

To me it simply says that the bbtUseDate has to be greater than equal to the
start date of the range and bbtUseDate has to be less than equal to the
ending date. Unless I am missing something, it seems to be okay to me?

Regards,
Kevin

This formula would not need to be array entered. Also the < and > are still in the wrong places.

Regards,
Mark Graesser
(e-mail address removed)

----- Kevin Stecyk wrote: -----

Scruff57

Try this:

 

[Mark Graesser]

Kevin,
I double checked the sample I put together and I transposed the start and end dates. Thanks for pointing that out. So the < and> where in the correct places in the original formula.

This means that the original should have worked when array entered.

Scruff, what do you mean by "no result". Or did you get this working already?

Regards,
Mark Graesser
(e-mail address removed)

----- Kevin Stecyk wrote: -----

Mark,

Yes, you are correct with regard to the arrays entered formula.

The greater and less than symbols I am less sure about.

The data falls within the range of the name ranges.

Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7

To me it simply says that the bbtUseDate has to be greater than equal to the
start date of the range and bbtUseDate has to be less than equal to the
ending date. Unless I am missing something, it seems to be okay to me?

Regards,
Kevin

 

[Scruff57]

Thanks for the suggestions!

I tried this formula, still getting 0 as a result when I should get . I switched around the <> and even entered the cell ranges directly (instead of the names) - still no go.

Does the cell format matter - as long as the dates are dates, it shouldn't matter if they are long or not - right? Would it matter if the date range (j7 and j8) were calculated from another cell?

I'm stumped - it all looks like it should work - it just doesn't


--
BRIAN ALLEN
Hi Scruff

Try
=SUMPRODUCT(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr))

not as an array formula.

--
Regards
Roger Govier
I am trying to set up an array formula to calculate data based on employee id. This same formula works with similar data on another worksheet, but for some reason I can't get it to work with this other worksheet. I know I am missing something stupid, perhaps someone can show me my error.

Worksheet1 - raw data
1 A B C D
2 Date center emplID total1

named ranges include
bbtAgentID = (C3:C20000)
bbtUseAppr = (D320000)
bbtUseDate = (A3:A20000)

The results should be based on the following parameters
Agent ID as listed in cell B1
Date range starting - J8
Date range ending - J7
results should be the sum of all the numbers in col D for that agentID between the dates listed in J7 and J8

the formula I am trying to get to work is
{=SUMPRODUCT(IF(($B$1=bbtAgentID)*(J8<=bbtUseDate)*(J7>=bbtUseDate),(bbtUseAppr)))}

I get no results with this formula, but I can if I filter the data on the worksheet. The data falls within the range of the name ranges. I use this same formula on another worksheet, changing the names to the appropriate ones and it works, What am I doing wrong here?

 

[Scruff57]

The result comes up as 0. When I filter the data manually, I should get 6

 

[Mark Graesser]

Scruff
If you would like me to take a look at your file you can e-mail it to me

Regards
Mark Graesse
(e-mail address removed)

 

[Mark Graesser]

In case anyone was wondering. The users agentID data had a bunch of following spaces in the table, so when he compared to B1 there was no match. This was corrected by putting the bbtAgentID inside the TRIM function

Mark Graesse

----- Mark Graesser wrote: ----

Scruff
If you would like me to take a look at your file you can e-mail it to me

Regards
Mark Graesse
(e-mail address removed)