Sum costs from unique records having a given type

[kaylands]

	A	B	C	D
1	Job#	Description	PO	Type
2
3	5400	311 11st		job
4	1101	NC Ward	350.00	deck
5	1102	IL Cotr		flex
6	1101	NC Ward	350.00	deck
7	5204	600 Pat Place		job
8	1102	IL Cotr	407.00	deck
9	1103	WA Brown		flex
10
11

<tbody>
</tbody>

I am working on a rather complex timesheet. One total I require is the total PO sum having Type = "deck" with a unique Job#. The job# may be listed more than once, and the PO for that job may be listed more than once - but the difficultly is that the rate for each job should only be added once per job. Also, the function needs to work even if some of the record lines are blank. So, in the example, the correct answer is $757. I am not using VBA. I thank you for your solutions.

 

[Andrew Poulsom]

Does this work for you?

=SUM(IF(FREQUENCY(IF($D$2:$D$8="deck",MATCH($A$2:$A$8&$D$2:$D$8,$A$2:$A$8&$D$2:$D$8,0)),ROW($D$2:$D$8)-ROW($D$2)+1)>0,$C$2:$C$8))

confirmed with Ctrl+Shift+Enter.

 

[kaylands]

Thanks! It works great. I wish I had a better understanding of how/why.

This is what I see happening. The match function returns the relative position of exact match info found in A2:A8 & D2:D8. If it is a "deck" record, then the relative match position is passed to the frequency function. The frequency function passes its results into the bin array specified by "Row(D2:D8)-Row(D2)+1". If the results of the frequency function are greater than 0, then the C column cell for that record is added to the sum.

I think I get what is going on except for:
1) The concatenated arrays used in the match function. What is matching and how can two columns be done at the same time?
and 2) The frequency function array input, bin output and return result. Seems like the bin array equation resolves to a number between 1-7.

 

[Andrew Poulsom]

The Evaluate Formula tool will show you how the formula works.