CountIfs Formula - Treating One Column Differently

[WednesdayC]

Hi All

I need some help please.

This is easier to explain with an example:-

[TABLE="width: 588"]
<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>[TR]
[TD="class: xl65, width: 46"]Ref
[/TD]
[TD="class: xl65, width: 91"]Invoice No[/TD]
[TD="class: xl65, width: 98"]Type[/TD]
[TD="class: xl64, width: 83"][/TD]
[TD="class: xl64, width: 89"][/TD]
[TD="width: 86"][/TD]
[TD="width: 95"][/TD]
[/TR]
[TR]
[TD="class: xl66"]A
[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl66"]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]A
[/TD]
[TD="class: xl63, align: right"]1000[/TD]
[TD="class: xl66"]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="align: right"]500[/TD]
[TD="class: xl66"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]B
[/TD]
[TD="align: right"]600[/TD]
[TD="class: xl66"]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]C
[/TD]
[TD="align: right"]700[/TD]
[TD="class: xl66"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="align: right"]800[/TD]
[TD="class: xl66"]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]D
[/TD]
[TD="class: xl63, align: right"]900[/TD]
[TD="class: xl66"]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]E
[/TD]
[TD="align: right"]950[/TD]
[TD="class: xl66"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]D
[/TD]
[TD="class: xl63, align: right"]900[/TD]
[TD="class: xl66"]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl65"]Current Result[/TD]
[TD="class: xl65"]Required Result[/TD]
[TD="class: xl65"]Current Result[/TD]
[TD="class: xl65"]Required Result[/TD]
[TD="class: xl65"]Current Result[/TD]
[TD="class: xl65"]Required Result[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]X[/TD]
[TD="class: xl65"]X[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Z[/TD]
[TD="class: xl65"]Z[/TD]
[/TR]
[TR]
[TD="class: xl66"]A
[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]B
[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]C
[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]D
[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl67"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]E
[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]
[/TR]
</tbody>[/TABLE]
<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