WednesdayC
Board Regular
- Joined
- Nov 7, 2010
- Messages
- 201
- Office Version
- 2016
- Platform
- MacOS
Hi All
I need some help please.
This is easier to explain with an example:-
<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:-
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
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>
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:-
HTML:
=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