Countif based on two criteria

Solved/Closed
ShootingFish - Feb 28, 2010 at 09:12 AM
 ShootingFish - Feb 28, 2010 at 03:48 PM
Hello,

I'm tinkering with a spreadsheet I use for my business and would like to create a count of the number of products dispatched on a date. I have included an example of the sheet I use, with names and such emitted of course.

You will see that I have used a list to create a drop down menu so I can change the packaging type in the future with minimal input. From this selection, I am looking to count how many products will be using each type of packaging on a dispatch date that is specified in the red cell (L14). A "countif" function is what I have been trying out and so far I haven't had much luck as I don't know how to tell it to look at the date in L14, match it with the same date in the B column and then count the number of each packaging type that have a "d" in their column for that date!

https://authentification.site/files/21184075/Example_Sheets.xls

It sounds complex to me, though I am sure there is some easy way!

ShootingFish

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
Very clever. I managed to get the total number of products on the date by using a countif and identifying the cell reference l14, not a specific date. This lead to problems though as it isn't flexible enough to specify two criteria.

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!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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.
0
No i didn't notice your "typing error".

The format in the uploaded sheet was correct and so i didn't pay too much attention to the wording in your message. It works just fine so i don't mind your mistake there.
0