Sumif with index and match if two criteria are met

Micwhsct

New Member
Joined
Aug 19, 2019
Messages
11
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.

Current Month Summary
Band 2Band 5 OrdinaryBand 5 SNGTotal Hours
XR02OtherSCOT G
Apr#N/A00#N/A=SUMIF(Mth,A7,INDEX(Grade,0,MATCH(B6,SUM(Time_Converted),0)))
May#VALUE!00#VALUE!'=SUMPRODUCT(Mth=$A$8)*(Grade=$B$6)*(Time_Converted)
Jun0000
Jul0000
Aug0000
Sep0000
Oct0000
Nov0000
Dec0000
Jan0000
Feb0000
Mar0000
#N/A00#N/A
<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> </tbody>

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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
Hi,
Why don't you use SUMIFS function for summarizing values if more than one condition are met?
 

Micwhsct

New Member
Joined
Aug 19, 2019
Messages
11
Below is the summary data tab that didn't post above - apologies.
The tips are blocked on my work web.
 

Micwhsct

New Member
Joined
Aug 19, 2019
Messages
11
That's what I was attempting above without success and now on here seeking help.
Thanks
 

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
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

New Member
Joined
Aug 19, 2019
Messages
11
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,726
Office Version
2007
Platform
Windows
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

New Member
Joined
Aug 19, 2019
Messages
11
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

New Member
Joined
Aug 19, 2019
Messages
11
Mentor82,
Thankyou very much - that was the problem and I was able to solve it easily.
Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,143
Messages
5,466,931
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top