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
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

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1571
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.


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.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1571 -
Have you attempted to correct the mangled range, as previously identified?
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