SUMIF(AND( formula

Ryan,

SUMIF can only be used in situations where the summing is based on a single condition.

Use instead:

(1)

=SUMPRODUCT((data!G2:G1000=$A$8)*(data!D2:D1000=$C$3)*(data!K2:K1000))

or equivalently

(2)
=SUM((data!G2:G1000=$A$8)*(data!D2:D1000=$C$3)*(data!K2:K1000))

The second is an array formula that must be entered by hitting CONTROL+SHIFT+ENTER at the same time (not just ENTER).

Aladin

=================

Aladin,
I was gone for a while. Could you point to the string that discovered no need for Control Shift Enter on Sumproduct? Also, did you have any luck with looking at the performance of that versus a CSE type array formula?
Thanks in advance,
ian

I should point out that A8 is a name,such as "Cary",and C3 represents a quarter, such as "Q2". and I only want data from Cary during Q2. The data, Total Labor hours, is in column K.

thanks
RRA
**************

, : SUMIF can only be used in situations where the summing is based on a single condition. : Use instead

Ryan -- It sounds like you did not get the desired result. Both formulas should work as intended. You have total labor hours per quarter. You should custom format the cell of the formula as [h]:mm (I reckon it's this format that you use in column A).

Aladin

Ian -- Could you rephrase this question? Mark and I had a pretty extensive exchange on SUMPRODUCT. I hope it is still available (we lost lots of threads of a week at this site). This function is less general than array formulas. Things that you multiply are treated inherently as arrays then summed.

Also, did you have any luck with looking at the performance of that versus a CSE type array formula?

In my own tests I didn't notice any performance difference between the equivalent SUMPRODUCT and array formulas. But, because these tests were not real life, & the data were not huge. Both types of formulas do equally well as ordinary formulas on these tests. I asked two people to try them out on their huge, complicated models. I didn't hear anything yet, alas.

Aladin

Ian -- Could you rephrase this question? Mark and I had a pretty extensive exchange on SUMPRODUCT. I hope it is still available (we lost lots of threads of a week at this site). This function is less general than array formulas. Things that you multiply are treated inherently as arrays then summed. Also, did you have any luck with looking at the performance of that versus a CSE type array formula? In my own tests I didn't notice any performance difference between the equivalent SUMPRODUCT and array formulas. But, because these tests were not real life, & the data were not huge. Both types of formulas do equally well as ordinary formulas on these tests. I asked two people to try them out on their huge, complicated models. I didn't hear anything yet, alas.

When I left, I was thinking that using sumproduct required entry in array fashion. Also, you and Mark were debating sumproduct's required number of arguments I think. In short, I was just wondering if there was agreement among the excel super-minds of whether the "traditional" array or sumproduct was superior.

thanks again,
Ian

Ian,

That thread is still available:

24938.html

& follow-ups.

No, it does not. So we can drop that CSE story whenever we propose a formula with SUMPRODUCT.

Also, you and Mark were debating sumproduct's required number of arguments I think.

I believe we had a plausible story on that point at the end.

Aladin

PS. Somewhere in that thread we listed a few interesting URLs on performance issues and one on the array formulas & DOTPRODUCT which lifts a few restrictions that SUMPRODUCT has.

Thanks, I'll check it out.

That thread is still available: 24938.html & follow-ups.