CountIfs Formula - Treating One Column Differently

[WednesdayC]

Hi All

I need some help please.

This is easier to explain with an example:-

Ref	Invoice No	Type
A	1000	X
A	1000	X
B	500	Y
B	600	X
C	700	Y
A	800	Z
D	900	Z
E	950	Y
D	900	Z
	Current Result	Required Result	Current Result	Required Result	Current Result	Required Result
	X	X	Y	Y	Z	Z
A	2	1	0	0	1	1
B	1	1	1	1	0	0
C	0	0	1	1	0	0
D	0	0	0	0	2	1
E	0	0	1	1	0	0

<colgroup><col style="mso-width-source:userset;mso-width-alt:1682;width:46pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:3328;width:91pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:3584;width:98pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:3035;width:83pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3254;width:89pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:3145;width:86pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:3474;width:95pt" width="95"> </colgroup><tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Georgia, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { background: yellow none repeat scroll 0% 0%; }.xl64 { font-weight: 700; }.xl65 { font-weight: 700; text-align: center; }.xl66 { text-align: center; }.xl67 { text-align: center; background: yellow none repeat scroll 0% 0%; }</style>

I am trying to count the number of occurrences of the raw data above, but when there are duplicate invoices, only count the result once.

The formula I have so far is:-

=COUNTIFS($A$3:$A$11,$A17,$C$3:$C$11,B$16)

However, I can't work out how to ignore the result for duplicate invoice numbers.

If a UDF is required here, please could you help me with the code.

Many thanks

Wednesday

 

[Canapone]

Hi

you could use a helper column in D3:D11 using in D3 to be copied down

=--(COUNTIFS($A$3:A3,A3,$C$3:C3,C3)=1)

to be read by a SUMIFS in B17 to be copied across

=SUMIFS($D$3:$D$11,$A$3:$A$11,$A17,$C$3:$C$11,B$16)

or in B17 to be confirmed with control+shift+enter

=SUM(IF(FREQUENCY(IF($A$3:$A$11&$C$3:$C$11=$A17&B$16,MATCH($C$3:$C$11,$C$3:$C$11,0)),ROW($3:$11)-ROW($A$3)+1),1))

Hope it helps

 

[WednesdayC]

Hi Canapone

Thank you so much.

The array formula suits my purposes perfectly, as I prefer not to introduce an extra column.

Regards

Wednesday