Hi
I am struggling with a SUMIF formula and need some help.
I am trying to build a Resource Capacity/Availability workbook and what I need to do in the SUMIF formula is as follows:
Total the values in Sheet1 Column I where the Value in Sheet1 Column A is equal to the value in "Skillset Availability" Column A is equal to the value in Sheet1 Column A and the month value in "Skillset Availability" Cell C1 is the same as the month value of each date in Sheet1 Column E.
Hope this makes sense. A sample workbook is attached to show where I have currently arrived at.
Any help would be appreciated.


Tony

Attached Files

Sample Res Pool Availability Gantt Chart Template.xlsx (142.7 KB, 12 views)

If I have understood you correctly.

Attached Files

Sample Res Pool Availability Gantt Chart Template_2.xlsx (140.1 KB, 12 views)

Hi Xor
Many thanks for your prompt response.
Your formula is returning a value of "0" whereas it should return "26".
Would you mind having another look.
Many thanks.
Tony

Out of curiosity, if I picked "BUSINESS SUPPORT" and equal your month of May, what answer do you expect?

I get 21 if I also understand your requirements.

=SUMPRODUCT(--(MONTH($C$1)=MONTH(Sheet1!$E$6:$E$14)),--($A3=Sheet1!$A$6:$A$14),Sheet1!$I$6:$I$14)

and this is in column E opn the Skillset Availability tab

Hi Jeffrey
That works perfectly, many thanks. Just one final request "how do I add the Year to each MONTH part of the equation" as my ranges will span two years as a minimum?
Thanks again for your feedback.
Tony

Hi Tony,

Try

=SUMPRODUCT(--(MONTH($C$1)&"|"&YEAR($C$1)=MONTH(Sheet1!$E$6:$E$1 4)&"|"&YEAR(Sheet1!$E$6:$E$14)),--($A3=Sheet1!$A$6:$A$14),Sheet1!$I$6:$I$14)

Brilliant - many thanks Jeffrey.

@OTPM please do not quote entire posts unnecessarily. It's just clutter and makes the thread hard to read. Thank tou

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

You're very welcome Tony...Happy to help!