Thread: Value error on sumproduct and sum array formula

Hi All,

I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.

One method has been using this sumproduct formula:

Code:

=SUMPRODUCT($P24:$P207="Temp")*($Q$24:$Q$207="A/R")*($AA$24:$AA$207)

The data in column AA is formatted as currency. The formula results in an error stating that a "value used in this formula is of the wrong data type"

A different sumproduct formula is used for temps that may be employed in an administrative role:

Code:

=SUMPRODUCT(--($P$24:$P$207="Temp"),--ISNUMBER(SEARCH("Admin",$Q$24:$Q$207))*($AA$24:$AA$207))

That results in the same error as indicated previously

Finally, I've tried an array formula as well:

Code:

=SUM(($P$24:$P$207="Temp")*($Q$24:$Q$207="BSG")*($AA$24:$AA$207))

That results in "wrong data type error" as well.

Any ideas? I would guess the problem lay with the data in column AA, but I've tried it as both currency and number data format and neither has worked. Oddly enough, a simple Sumif formula, using the data in column AA seems to work, but an if(sumif( formula does not.

I've attached a sample spreadsheet. The formulas I'm having problems with begin in L302.

Thanks in advance for the help

Cheers,
Tim

I would remove the multiplication ("*") and stick with individual arguments e.g.

Code:

=SUMPRODUCT(--($P24:$P207="Temp"),--($Q$24:$Q$207="A/R"),($AA$24:$AA$207))

=SUMPRODUCT(--($P$24:$P$207="Temp"),--ISNUMBER(SEARCH("Admin",$Q$24:$Q$207)),($AA$24:$AA$207))

Does that work for you?