Countif based on two criteria
Solved/Closed
Related:
- Countif based on two criteria
- We couldn't find an account matching the login info you entered, but found an account that closely matches based on your login history ✓ - Facebook Forum
- Transfer data from one excel worksheet to another automatically based on criteria ✓ - Excel Forum
- Based on the values in cells b77 b81 c77 - Excel Forum
- We couldn't find an account matching the login info you entered, but found an account that closely matches based on your login history. - Facebook Forum
- Based on the values in cells b77 ✓ - Excel Forum
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 28, 2010 at 02:20 PM
Feb 28, 2010 at 02:20 PM
You are trying to do two things
1. Label all dates as DATES (your case : Column B
2. Label the product matrix as PRODUCTS (in your case: Column C:F)
3. Label the packaging method as DATES_PRODUCTS (in your case C3:F3)
4. As per your example, lets say date against which u want to check is L14
to find count of all cells where date matches value in L14 and matrix have a value of "d", you can use SUMPRODUCT((DATES=L14) *(1*(DATES_PRODUCTS="d")))
to find count of all cells where date matches value in L14 and matrix have a value of "d", and packaging type is boxed, you can use
=SUMPRODUCT((DATES=L14)*(1*(DATES_PRODUCTS="d"))*(1*(PRODUCT_PACKING="Boxed")))
I have put the file on shared site
https://authentification.site/files/21192084/ConditionalSum.xls
1. Label all dates as DATES (your case : Column B
2. Label the product matrix as PRODUCTS (in your case: Column C:F)
3. Label the packaging method as DATES_PRODUCTS (in your case C3:F3)
4. As per your example, lets say date against which u want to check is L14
to find count of all cells where date matches value in L14 and matrix have a value of "d", you can use SUMPRODUCT((DATES=L14) *(1*(DATES_PRODUCTS="d")))
to find count of all cells where date matches value in L14 and matrix have a value of "d", and packaging type is boxed, you can use
=SUMPRODUCT((DATES=L14)*(1*(DATES_PRODUCTS="d"))*(1*(PRODUCT_PACKING="Boxed")))
I have put the file on shared site
https://authentification.site/files/21192084/ConditionalSum.xls
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 28, 2010 at 03:44 PM
Feb 28, 2010 at 03:44 PM
I hope you did notice two mistakes on my part
Mistake #1
2. Label the product matrix as PRODUCTS (in your case: Column C:F)
Should read as
2. Label the product matrix as DATES_PRODUCTS(in your case: Column C:F)
Mistake #2
3. Label the packaging method as DATES_PRODUCTS (in your case C3:F3)
should have been
3. Label the packaging method as PRODUCT_PACKING (in your case C3:F3)
As far as book goes, thanks. I guess, 1 down and a couple of thousand more purchasers to find. Thanks for vote of confidence.
Mistake #1
2. Label the product matrix as PRODUCTS (in your case: Column C:F)
Should read as
2. Label the product matrix as DATES_PRODUCTS(in your case: Column C:F)
Mistake #2
3. Label the packaging method as DATES_PRODUCTS (in your case C3:F3)
should have been
3. Label the packaging method as PRODUCT_PACKING (in your case C3:F3)
As far as book goes, thanks. I guess, 1 down and a couple of thousand more purchasers to find. Thanks for vote of confidence.
Feb 28, 2010 at 02:29 PM
What you have done is similar in some ways but essentially better. I knew that the method was to ask the formula to look at the criteria and to calculations based on the findings. My problem was how to put that into language that the computer could understand.
Thank you muchly for your help once again, if you ever write a book about computer coding then point me in the direction of your online store and I'll be buying it in two clicks!!