Using SUMIFs to sum muliple criteria from ranges

[tominghana]

Hi therre

I’m using SUMIFs to sum data from a list of timesheet details based on

-project code: '2014 replicon data store’!A:A (range),$B6 (code to include)
-staff category: '2014 replicon data store’!E:E (range),$F6 (category to include)
-returning total number of hours meeting the above conditions: '2014 replicon data store’!D:D (range)

This is the formula that returns the above:

=(sumifs('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B6,'2014 replicon data store'!E:E,$F6)

However, as some of our projects have more than one code, I’d like to expand this formula so that if I add more project codes below B6, it also includes these in the search.

A manual way to do this across codes in B6 and B7 is:

=(sumifs('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B6,'2014 replicon data store'!E:E,$F6))+(sumifs('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B7,'2014 replicon data store'!E:E,$F6))

However, it will be prohibitively laborious to have to roll this out across all my formulas, as I would like the option to count up to 10 different project codes. So I’m looking for something like:

=(sumifs('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B6:$B16,'2014 replicon data store'!E:E,$F6)

…where I turn $B6 to $B6:$B16 - but this doesn’t work. Is there another way to modify this formula so that it will look for more than one project code in the SUMIF?

Many thanks from a newbie!

Tom

[Bernie Deitrick]

Not in a single cell. In a cell on row 6, use your existing formula, with one change (bolded)

=(sumifs('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B6,'2014 replicon data store'!E:E,$F$6)

and copy down to row 16, then sum those cells.

[Jonmo1]

Try

=SUMPRODUCT(sumifs('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B6:$B16,'2014 replicon data store'!E:E,$F6)

[tominghana]

Thank you - that doesn't seem to have any effect...(it's not pulling in data from the second project code...)

[Jonmo1]

Can you post a sample book?

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[tominghana]

Hi there

Thank you. I've uploaded an example workbook (I think). Thanks for your help!

Tom

[Jonmo1]

You haven't entered a value into the 2nd (or beyond) project code.
You only have a value in B6, nothing in B7 - B16

In X6 you have
=SUMPRODUCT(SUMIFS('2014 replicon data store'!D:D,'2014 replicon data store'!A:A,$B6:$B16,'2014 replicon data store'!E:E,$F6))

Once I enter say "Test2" into B7, the formula in X6 updates from 3 to 7

[tominghana]

Ah yes- thanks.

I'm actually working in Google Spreadsheets, which doesn't seem to interpret the formula in the same way. I exported for this example and forgot to change the formula back from what you'd suggested - or to test whether it worked in Excel!

It does indeed already do exactly what I wanted it to in the example I provided....I suppose it's sacrilege to ask if there's any reason why it wouldn't work in GS?

Tom