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.

lcY8168tO9Sy7jcPH8ikvZ5El43fD r2jnSWDcB33QwFrhkYCxwa2AscBbwz6egBwgAAKmCBAgAyBkdogeYZ0n8h6l5DQAoCngnSMlAH2BroA wkyD6f3NptDaSJwn3AAAAAElFTkSuQmCC


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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi,
Why don't you use SUMIFS function for summarizing values if more than one condition are met?
 
Upvote 0
Below is the summary data tab that didn't post above - apologies.
The tips are blocked on my work web.
90lp27sDSMp3hGOPGY6xSZHmZhc2xIrsftpYUq2WFErsu8zKxXDBXKlSAF q2sRgrVtjVvSKV2GV8Xe02htyYv8mnksBfvFhwKgUoVBy3NHGw4qf5vqQIHz8lJm7 WY7hukX2v0CZt IlfZdYhuXq eOTuM8T77ph 1mUzhFn3Qh8k6GxwDmDxgKHg8YCJwH fIr0AAmCIGKFEiBBECmjQfQA0yyJfy82rwmCyAr0TpCcQX2A4aA wEYC4P8BcwrSlQXPCqkAAAAASUVORK5CYII=
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Upvote 0
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
 
Upvote 0
Mentor82,
Thankyou very much - that was the problem and I was able to solve it easily.
Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top