SumIf with 2 arguments

[Snuker]

I have a spreadsheet with a list of jobs I am resourcing with the following attributes as row headings ....

Resource Name
Job Code
Project Name
Chargable or Non Chargable
Hours charged

And I have the column headings as the date week by week (simple stuff)

I have used SumIf() to report in another list the total number of hours charged by person simply by having the list of names to check and SumIf() the 'Resource Name' list and then sum'ing the 'Hours Charged' column for if a match is found.

This works perfectly so now I have a table of time charged by person - woohoo.

Now I need to split out JUST the Chargable time. So I kinda want to say .... 'if the Resource Name is the same AND the Resource work is listed as Chargable then Sum the Hours Charged .... but SumIf doesn't allow more than one argument in the if .... or does it ?

Thanks in advance,

Christopher

PS> I want the resource's Chargable hours for each day and displayed in the spreadsheet so a pivot table does not work either - I need a formula ?

Help !

[arthurbr]

Hi Christopher
Have a search on SUMPRODUCT on the board
You'll get lots of answers

[martindwilson]

i dont think its that simple if all the jobs are down one column say a
with info in b , you'd have to offset the sum product.
like
=SUMPRODUCT(--(B1:B17="martin"),--(B4:B20="chargeable"),(B5:B21))
see attached

[Snuker]

This is EXCELLENT thanks for your quick and great help - it works

I have copied and pasted the formula and edited the references to my cell and it works - thanks.

BTW what is the deal with the '--' before the brackets, there is nothing in the Excel help on using "Sumproduct" in this way - more for actually products than summing

any help ?

[arthurbr]

They are a method of coercion, which is a way to convert a Boolean value (TRUE or FALSE) to its numeric equivalent (1 or 0). There are several ways to accomplish it, (adding a zero, or multiplying by 1 as examples). One of the, if not the, fastest and most efficient ways is to use two negation operators together, which changes the sign twice, resulting in a numeric value.


I suggest you Google around for SUMPRODUCT and "unary operator" you'll find lots of answers(personally I use the * operator instead of --,)