Sumif with index and match if two criteria are met

[Micwhsct]

Hi,

Looking help in constructing a formula to help me interrogate a large data area which if 2 criteria or met then sum the associated hours worked.
Basically I want a summary report that gives me the total hours worked by a particular grade for each month as we go through the year.

Below is the summary table I am looking to complete, aong with 2 formula's I tried without success.

[TABLE="width: 592"]
<colgroup><col width="64" style="width: 48pt;" span="5"> <col width="18" style="width: 14pt; mso-width-source: userset; mso-width-alt: 658;"> <col width="451" style="width: 338pt; mso-width-source: userset; mso-width-alt: 16493;"> <tbody>[TR]
[TD="width: 192, bgcolor: transparent, colspan: 3"]Current Month Summary
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 18, bgcolor: transparent"][/TD]
[TD="width: 451, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]Band 2[/TD]
[TD="width: 64, bgcolor: transparent"]Band 5 Ordinary[/TD]
[TD="width: 64, bgcolor: transparent"]Band 5 SNG[/TD]
[TD="width: 64, bgcolor: transparent"]Total Hours[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 451, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]XR02[/TD]
[TD="width: 64, bgcolor: transparent"]Other[/TD]
[TD="width: 64, bgcolor: transparent"]SCOT G[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=SUMIF(Mth,A7,INDEX(Grade,0,MATCH(B6,SUM(Time_Converted),0)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent, align: center"]#VALUE![/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: center"]#VALUE![/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]'=SUMPRODUCT(Mth=$A$8)*(Grade=$B$6)*(Time_Converted)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sep[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Oct[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Nov[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: center"]#N/A
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Below is a summary of the main data tab - note all columns are named ranges.

Really appreciate any help or advice provided.
Thanks in advance.

 

[Mentor82]

Hi,
Why don't you use SUMIFS function for summarizing values if more than one condition are met?

 

[Micwhsct]

Below is the summary data tab that didn't post above - apologies.
The tips are blocked on my work web.

 

[Micwhsct]

That's what I was attempting above without success and now on here seeking help.
Thanks

 

[Mentor82]

Hi,
As far what I can see you tried SUMIF function not SUMIFS.
In my opinion your further clarification is required because from the thread you posted it's not cleared for me what your need is.
How your source database looks loke? From which column/columns you want to summarize values in which column/columns you store the conditions you want to check if two specific of them are met?

 

[Micwhsct]

Thanks Mentor82


Unfortunately I am having issues posting screenshots due to work blocking these sites.
You are correct - I have only tried SUMIF and not SUMIFS.
As I can't post the image below is the named ranges I need to include in my formula.
Grade
Mth
Time_Converted

Hopefully this is of help.
Ps. Do you have any other tips on how to copy in small screenshots.
Thanks

 

[DanteAmor]

Upload an excel image:
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

In the examples you must put the values ​​you have and the expected result.

 

[Micwhsct]

Thanks Dante Amor

Dropbox is also a prohibited website - so really strugling with sending screenshots, but thanks for the tip.
I have now sorted my problem by using SUMIFS not SUMIF.
Apologies and thanks again for all efforts to assist me.
Michael

 

[Micwhsct]

Mentor82,
Thankyou very much - that was the problem and I was able to solve it easily.
Thanks again.