an array formula in a dynamic table

[y2gabs]

Hey guys,

I have created an array formula for a SUMIF statement that draws from a database table import (weekly copy paste to a worksheet in the workbook) based on two conditions. Because the SUMIF is based on two conditions, I have to make it an array formula. Typically when I do this with other 1-condition SUMIF statements, the data in my table is updated automatically but because my new SUMIF statement is an array formula, that cell is not updated automatically until I go to it and press ctrl+shift+enter, then it updates. Is there a macro or utility out there that I can run on a weekly basis to do the ctrl+shift+enter 'ing automatically?

Hope this makes sense!!

Thanks!

J.

 

[xld]

Array formulae update automatically, so there must be some reason yours don't. Is calculation set to manual?

 

[Norie]

Why do you have to make it an array formula?

Could you not use SUMPRODUCT?

 

[y2gabs]

Thanks for the replies guys!

I'm not familiar with SUMPRODUCT but I'll do some research on it tonight. I had to go with an array because I'm using multiple conditions in the SUMIF. Here's my formula:

{=SUM(IF((Week1!$A1:$A4000=$A$2)*(Week1!$J1:$J4000<>"Lost")*(Week1!$J1:$J4000<>"Suspended")*(Week1!$J1:$J4000<>"Abandoned")*(Week1!$J1:$J4000<>"Won")*(Week1!$E1:$E4000=$A$45),(Week1!$D1:$D4000)*0.6))}

It's looking through active opportunities in our CRM (ie. status not "Lost", "Suspended", etc) for expected revenue, per salesman, per week (each sheet represents a week and contains the dbase-table export for that week). note that the CRM has multiple active status labels so it's easier to lookup against <> inactive status labels.

xld, I wasn't aware that there was a setting to specify whether or not the array is manual. Where do I set it to be dynamic/automatic?

Thanks for the help!!

J.

 

[y2gabs]

Ok, I just checked out SUMPRODUCT and it doesn't look like it's going to work for me. SUMPRODUCT treats array entries that are not numeric as if they were zeros and both of my conditions are alphanumeric.

J.

 

[xld]

Thanks for the replies guys!

I'm not familiar with SUMPRODUCT but I'll do some research on it tonight. I had to go with an array because I'm using multiple conditions in the SUMIF. Here's my formula:

{=SUM(IF((Week1!$A1:$A4000=$A$2)*(Week1!$J1:$J4000<>"Lost")*(Week1!$J1:$J4000<>"Suspended")*(Week1!$J1:$J4000<>"Abandoned")*
(Week1!$J1:$J4000<>"Won")*(Week1!$E1:$E4000=$A$45),(Week1!$D1:$D4000)*0.6))}

It's looking through active opportunities in our CRM (ie. status not "Lost", "Suspended", etc) for expected revenue, per salesman, per week (each sheet represents a week and contains the dbase-table export for that week). note that the CRM has multiple active status labels so it's easier to lookup against <> inactive status labels.

SP would do this just as well

=SUMPRODUCT(--(Week1!$A1:$A4000=$A$2),
            --(Week1!$J1:$J4000<>"Lost"),
            --(Week1!$J1:$J4000<>"Suspended"),
            --(Week1!$J1:$J4000<>"Abandoned"),
            --(Week1!$J1:$J4000<>"Won"),
            --(Week1!$E1:$E4000=$A$45),Week1!$D1:$D4000)*0.6

xld, I wasn't aware that there was a setting to specify whether or not the array is manual. Where do I set it to be dynamic/automatic?

Tools>Options>Calculation

 

[Norie]

Ok, I just checked out SUMPRODUCT and it doesn't look like it's going to work for me. SUMPRODUCT treats array entries that are not numeric as if they were zeros and both of my conditions are alphanumeric.

J.

I'm no formula (especially SUMPRODUCT) expert but I don't think that's the case.

Mind you I don't quite know what you mean by 'array entries'.