sumif multiple columns

[Mr2017]

Hi

I'd like to create a sumif formula that sums up data based on criteria in multiple columns, but not sure if this is possible?

In the table below, I'd like to sum up the number of HOURS for Greg (a user) that are

i) approved (column G)
ii) billable (column H)
iii) from the month of August (column I)
iv) from the 16/17 financial year

Then answer should be 2 (because the 5 hours in column F are non-billable - see column H). So 1 hour from the 4th of March and 1 hour from the 5th of March would be summed up to give the value 2, because those hours fulfill all the above criteria.

Is that possible? A basic part of the results would like like the second table below. Thanks in advance!

[TABLE="width: 757"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Project[/TD]
[TD]Work Item[/TD]
[TD]User[/TD]
[TD]Date[/TD]
[TD]Hours[/TD]
[TD]State[/TD]
[TD]Billable?[/TD]
[TD]Month Name[/TD]
[TD]FY[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X Calibur[/TD]
[TD]Checks[/TD]
[TD]Greg [/TD]
[TD]03-Mar-16[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Non-Billable[/TD]
[TD]Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X Calibur[/TD]
[TD]Checks[/TD]
[TD]Greg [/TD]
[TD]04-Mar-16[/TD]
[TD]1[/TD]
[TD]Approved[/TD]
[TD]Billable[/TD]
[TD]Mar[/TD]
[TD]16/17[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X Calibur[/TD]
[TD]Checks[/TD]
[TD]Greg [/TD]
[TD]05-Mar-16[/TD]
[TD]1[/TD]
[TD]Approved[/TD]
[TD]Billable[/TD]
[TD]Mar[/TD]
[TD]16/17[/TD]
[/TR]
</tbody>[/TABLE]


Second table

[TABLE="width: 505"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User[/TD]
[TD]March[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]Sum of approved hours in March 2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Value should be 2 (1 hour on 4th & 1 hour on 5th March)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

If you have Excel 2007 or later, you should be looking at SUMIFS

SUMIFS function - Office Support

 

[tcardwell]

Kinda like:
=SUMIFS(Hours,State,"Approved",Billable?,"Billable",Month_Name,"Mar",FY,"16/17")
Named ranges were created from your column headers to make formula more readable.

 

[Mr2017]

Thanks Spiller BD - that was the most useful article I read on sumifs - explained things clearly!

Thanks again.

 

[Mr2017]

Thanks tcardwell - yes, I used that format with named ranges to construct the formula. Previous articles I'd read weren't helpful, but I was able to compile what you described after reading the article Spiller BD posted. Thank you both.