Sum if function??

[BUSJEDWA]

Please refer to the attached doc:

In sheet 3, column R (revenue hours) I need to report the following:

Match B3 with “Timesheets” E:E and sum is the total hours in V:V where D:D equals 001 (there will be more than one line of 001 to sum up) or 006.

[martindwilson]

=SUMPRODUCT((Timesheets!$$2:$E$20=B3)*(Timesheets!$D$2:$D$20="001")*(Timesheets!$V$2:$V$20))
or using your named ranges
=SUMPRODUCT((DATA5=B3)*(DATA4="001")*(DATA22))

[DonkeyOte]

Some will suggest a Sumproduct

Sheet3!R3
=SUMPRODUCT(--(Timesheets!$E$2:$E$1000=$B3),--(Timesheets!$D$2:$D$1000="001"),Timesheets!$V$2:$V$1000)

Given that you're running these in a matrix you will invariably end up with a lot of them and they are inefficient in terms of performance.

Better to create a concatenation key on Timesheets and use non-Sumproduct/CSE Array formulae, eg:

Timesheets!Y2: =D2&":"&E2
copied down for all rows

Sheet3!R3
=SUMIF(Timesheets!$Y$2:$Y$1000,"001:"&$B3,Timesheets!$V$2:$V$1000)

Not as elegant but a lot more efficient especially when used in large quantities.

[BUSJEDWA]

Thanks very much, this is summing up 001 in "Timesheets" column D only, i need to sum up 001 and 006 (sorry not or), i'm flddling with the formuale to add it but with little joy ...

[martindwilson]

=sumproduct((data5=b3)*(or(data4="001",(data4="006")))*(data22))
but if you are always going to have only001 /006 rthen just use sumif on the name
=SUMIF(DATA5,B3,DATA22)

[DonkeyOte]

To conduct an OR within a Sumproduct utilise the + operator (if you have numerous test you can switch to an ISNUMBER(MATCH test with an inline array of accepted values))

=SUMPRODUCT(--(Timesheets!$E$2:$E$1000=$B3),(Timesheets!$D$2:$D$1000="001")+(Timesheets!$D$2:$D$1000="006"),Timesheets!$V$2:$V$1000)

Given 001 + 006 can not exist in D simultaneously the above should work without incident.

It would still be quicker to conduct 2 SUMIFs using Concatenation (IMO)

=SUMIF(Timesheets!$Y$2:$Y$1000,"001:"&$B3,Timesheets!$V$2:$V$1000)+SUMIF(Timesheets!$Y$2:$Y$1000,"006:"&$B3,Timesheets!$V$2:$V$1000)

[martindwilson]

dk it doesnt make a lot of difference i just tried it with 5000 rows and couldnt see any lag in calculation!
mind you why + ?
(or(data4="001",(data4="006")) seems to work ok or am i missing something? or is it just a save on typing thing?

[DonkeyOte]

dk it doesnt make a lot of difference i just tried it with 5000 rows and couldnt see any lag in calculation!
mind you why + ?
(or(data4="001",(data4="006")) seems to work ok or am i missing something? or is it just a save on typing thing?

I can tell you categorically that using Arrays / Sumproduct with large ranges used extensively will cripple calculation... the volume of calcs performed in these methods is vastly greater than using SUMIF/COUNTIF ... if you look around the board you will find countless examples of this... too many people (IMHO) advocate Arrays/Sumproducts when clearly the OP intends to use them in Volume... better to advise alternate approaches (be it Concatenation, Pivot Table etc etc...)

Re: + Operator ... OR's in CSE / Sumproduct do not work like a traditional OR... if you check your results carefully you will find your formula does not work.

[BUSJEDWA]

excellent, thankyou so much. Happy to close the thread!!