Menu
Ask a question Report

Summarise hours incurred by month based on multiple criteria [Solved]

Posts
7
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
August 19, 2019
 - - Latest reply: Micwhsct
Posts
7
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
August 19, 2019
 - Aug 19, 2019 at 07:50 AM
Excel Ask a question Report
Hi,

I want to summarise hours incurred mthly (from the time_converted column) based on the grade and agency but can't seem to get the below formula working.

=SUMIF(Mth,A6,INDEX(Grade,0,MATCH(B5,SUM(Time_Converted),0)))

Aappreciate any help and thanks in advance.

Michael
See more 

1 reply

Report Respond to ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342
0
Thank you
Is time-converted a named range?

What value is in cell B5 that you are trying to match?

Not only that, but the syntax of match is as follows:

MATCH (LOOKUPVALUE, LOOKUPARRAY, MATCHTYPE).

Your match is summing on the second lookuparray. So your array is not constructed correctly, as it should be in the form of cellAdress1:cellAdress2.


Report Respond to Micwhsct
Micwhsct
Posts
7
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
August 19, 2019
 -
Yes, 'time_converted' is named range.

B5 represents a specific grade of staff within the named range 'Grade'.

A B C D E
5 XR02 Other SCOT G Total Hours
6 Apr #N/A 0 0 #N/A
7 May 0 0 0 0
8 Jun 0 0 0 0

To clarify what I want to do is look for a specific grade of staff within the named range 'Grade' and then sum total hours related to that month from the time_converted column.

This is an extract of the main data source tab named ranges needed for the formula.
J P Q R S
10 Grade Cost Cente Time Converted Agency Mth
11 XR02 E0A072 10.75 Other Apr
12 XR02 E0A072 10.75 Other Apr
13 XR05 E0A072 10.75 SCOT G Apr
14 XR05 E0A072 10.75 SCOT G Apr
15 XR02 E0A072 8.5 Other Apr

Thanks again.
Report Respond to ac3mark
ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342 -
Have you attempted to correct the mangled range, as previously identified?
Report Respond to Micwhsct
Micwhsct
Posts
7
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
August 19, 2019
 -
Yes - but without success unfortunately.
I'm not as smart as I thought on excel - but at least I try.
Respond to ac3mark
Reply Ask a question