Make sure to also watch my video on how to use the Unique formula when you want to apply a filter based on more than one criteria : ruclips.net/video/aRLK-qO2mqo/видео.html
Hi there! I like your video. Very instructional. I am looking for a different type of formula though. I have a list of IDs (worker numbers) with (amongst others) also a FTE% in the same table. I am looking for the SUM of the FTE values for the FIRST occurrence of each workerID. I do NOT want to work with pivots, VBA, or anything else that would need to be refreshed if possible. So i'm thinking .. formulas. Is there a solution for my question?
Hi @guyfalleyn4249, Thanks for your kind words and for watching the video! For summing the FTE values for the first occurrence of each worker ID using formulas only, you can utilize an array formula that combines SUMPRODUCT, UNIQUE, and FILTER. This can help you achieve what you're looking for without needing to refresh pivot tables or use VBA. Here’s a basic outline of how the formula might look: =SUMPRODUCT(--(A2:A100=UNIQUE(FILTER(A2:A100, B2:B100="Your Condition"))), C2:C100) This formula assumes your worker IDs are in A2:A100, your FTE% are in C2:C100, and you have a specific condition in B2:B100. Adjust the ranges and conditions to fit your data. I hope this helps! If you have more questions or need further clarification, feel free to ask. Cheers, Mehran
Hello @rajunani7371, Thank you for your comment! I'd like to help you with your Excel query, but I need a little more clarity to provide the best solution. It seems you want to count unique names under certain conditions related to their ranks and perhaps exclude certain criteria in column B (Mobile numbers). Could you please specify: What is the condition you mentioned for the ranks in column C? Are you looking to exclude certain mobile numbers, or is there another condition related to column B? Once I have a bit more detail, I’ll be better equipped to assist you. Looking forward to your response! Best regards, Mehran
Hi and thanks for the comment. To modify the given formula to return a count of unique values that meet a specific condition, instead of listing the unique names, you can wrap the UNIQUE(FILTER(...)) portion in a COUNTA function. The COUNTA function counts the number of non-empty values in a range. Here's how you can adjust the formula: =COUNTA(UNIQUE(FILTER(C6:C33, D6:D33=H5))) This formula first filters the range C6:C33 based on the condition specified in D6:D33=H5. It then finds unique values from the filtered results. Finally, COUNTA counts these unique values. The result will be the count of unique values that match the condition, rather than listing the values themselves.
Make sure to also watch my video on how to use the Unique formula when you want to apply a filter based on more than one criteria : ruclips.net/video/aRLK-qO2mqo/видео.html
Hi there! I like your video. Very instructional. I am looking for a different type of formula though.
I have a list of IDs (worker numbers) with (amongst others) also a FTE% in the same table. I am looking for the SUM of the FTE values for the FIRST occurrence of each workerID.
I do NOT want to work with pivots, VBA, or anything else that would need to be refreshed if possible. So i'm thinking .. formulas. Is there a solution for my question?
Hi @guyfalleyn4249,
Thanks for your kind words and for watching the video! For summing the FTE values for the first occurrence of each worker ID using formulas only, you can utilize an array formula that combines SUMPRODUCT, UNIQUE, and FILTER. This can help you achieve what you're looking for without needing to refresh pivot tables or use VBA.
Here’s a basic outline of how the formula might look: =SUMPRODUCT(--(A2:A100=UNIQUE(FILTER(A2:A100, B2:B100="Your Condition"))), C2:C100)
This formula assumes your worker IDs are in A2:A100, your FTE% are in C2:C100, and you have a specific condition in B2:B100. Adjust the ranges and conditions to fit your data.
I hope this helps! If you have more questions or need further clarification, feel free to ask.
Cheers, Mehran
I have a data in (a,b,c column) and (A,names B, Mobile numbers and C, Rank) here i want to Name wise and Rank condition of B column unique count
Hello @rajunani7371,
Thank you for your comment! I'd like to help you with your Excel query, but I need a little more clarity to provide the best solution. It seems you want to count unique names under certain conditions related to their ranks and perhaps exclude certain criteria in column B (Mobile numbers).
Could you please specify:
What is the condition you mentioned for the ranks in column C?
Are you looking to exclude certain mobile numbers, or is there another condition related to column B?
Once I have a bit more detail, I’ll be better equipped to assist you. Looking forward to your response!
Best regards,
Mehran
Instead of the names how would you get the value as a count?
Hi and thanks for the comment. To modify the given formula to return a count of unique values that meet a specific condition, instead of listing the unique names, you can wrap the UNIQUE(FILTER(...)) portion in a COUNTA function. The COUNTA function counts the number of non-empty values in a range. Here's how you can adjust the formula:
=COUNTA(UNIQUE(FILTER(C6:C33, D6:D33=H5)))
This formula first filters the range C6:C33 based on the condition specified in D6:D33=H5. It then finds unique values from the filtered results. Finally, COUNTA counts these unique values. The result will be the count of unique values that match the condition, rather than listing the values themselves.