Count Multiple Cells If Multiple Conditions with Date

Stephanie Stoewe

Hello!  I am working on linking a database to a results sheet and I need several metrics pulled out. I have Supplier, Defect Column 1, Defect Column 2, Defect Column 3, and date. What I ultimately need to do is create a January through December count of defects per Supplier. 

So I have Jan, Feb, Mar, ... Dec. on one axis, and Suppler 1, Suppler 2, ... Supplier 5 on another axis. I want to tally all Defects for each supplier per month. 

=SUMIF({Supplier}) = "Supplier 1", AND(MONTH({Inspection Date}) = 1)

This is all I have so far, I feel like I'm on the right track, but now I need to pull in responses from the Defect Columns that match the supplier name, and 1 (in hopes that means January). 

Any ideas? 

Paul Newcome

Try something along the lines of 

 

=COUNTIFS({First Defect Column Range}, ISTEXT(@cell), {Supplier}, @cell = Supplier@row, {Inspection Date}, MONTH(@cell) = 1)

 

Use that for each Defect Column and then add them together. That will give you the total count of defects per supplier.

Stephanie Stoewe

Awesome!  Where can I put the actual Supplier name it is supposed to look for, i.e. "ABCCo"? 

Paul Newcome

From the sound of it, your original sheet is set up as

Supplier      Defect 1      Defect 2      Defect 3      Date

.

.

.

.

You could use a similar setup.

Supplier      Defect Count

.

.

.

.

In the supplier column of your new sheet you can have each supplier listed just once. The formula will automatically look for the supplier name in that column for whatever row the formula is sitting on.

Stephanie Stoewe

Yes, how I have this laid out is Suppliers are Column headings, then Months are rows.

So...

          Supplier 1    Supplier 2   Supplier 3    Supplier 4 ... etc.

Jan

Feb

Mar

...

 

What I'd like to do is to have the formula look at my source sheet and find every entry for Supplier 1 and count up several defect columns based on the month. 

 

Paul Newcome

Ok. Try this. Put the Supplier's name in row one. Put a helper column (wherever as it can be later hidden) that has each Month number in the appropriate row. Referencing those cells in your formula will provide flexibility. (Column Headers are in BOLD)

Month   Supplier 1    Supplier 2   Supplier 3      Month#

               Supplier 1     Supplier 2    Supplier 3

Jan                                                                                1

Feb                                                                                2

Mar                                                                                3

...

.

.

You could then use...

 

=COUNTIFS({First Defect Column Range}, ISTEXT(@cell), {Supplier}, @cell = [Supplier 1]$1, {Inspection Date}, MONTH(@cell) = [Month#]@row)

.

You can then dragfill this over and down and it will automatically look for the supplier name in row one of whatever column the cell is in AND the month number in accordance with whatever row it is in.

 

You could even put the year in the sheet somewhere and reference that (say it's in the top left corner which would be $Month$1). You could then use...

 

=COUNTIFS({First Defect Column Range}, ISTEXT(@cell), {Supplier}, @cell = [Supplier 1]$1, {Inspection Date}, AND(MONTH(@cell) = [Month#]@row, YEAR(@cell) = $Month$1))

.

From there you can indent the months to make them children rows of the year. Copy/paste this whole block of data in the same sheet but below, change the year in the month column for that block to the following year and you've got consistent tracking of data that is easily maintained year to year. If going this route, you would change the YEAR criteria slightly to reference the PARENT cell instead of the top left cell in the sheet.

 

=COUNTIFS({First Defect Column Range}, ISTEXT(@cell), {Supplier}, @cell = [Supplier 1]$1, {Inspection Date}, AND(MONTH(@cell) = [Month#]@row, YEAR(@cell) = PARENT($Month@row))).

.

You could also add a blank row between the supplier names (row 1) and the start of the table (row 2 is now a blank row and JAN is now on row 3). Put your year in Month3 and in the remaining columns for that row you could use a simple

 

=SUM(CHILDREN())

 

to have Year To Date row for each supplier. Since the Months are indented below the year, you can collapse (for example) the row containing 2018 data for a YTD summary and leave 2019 expanded since that is the current year.

Stephanie Stoewe

Hmm. Unparsed. Changes I've made to my source sheet now include Month and Year so I can easily call to those when I need to.  

Trying a few variations.  {Fab MAJ} from ref. sheet = Defect to count, and Supplier Name from ref. sheet is "Other".

=COUNTIFS({Fab MAJ}, ISTEXT(@cell), {Supplier}, @cell = [Other]$1, AND{Month}, @cell = [1]@row, AND({Year}, @cell = [2019])

 

 

Paul Newcome

Can you share a screenshot of both sheets? Being able to visualize it may help.

Stephanie Stoewe

No problem. The summary screen has month (1-12), Year (2019), Supplier 1, Supplier 2, etc.... 

Then the reference sheet has the  month (1-12), Year (2019, 2020...etc.) and then Supplier 1, Supplier 2...  What I want it to do is add all of the purple cells to the right up.

Does this help?

Paul Newcome

Ah! Got it now. Ok... The easiest way to do this would be to add an additional column to your master sheet. We'll call the new column [Total Defects]. In this column you would put 

.

=SUM([Fab MIN]@row:[Lab MAJ]@row)

.

This will add up all defects for each row. In your summary sheet I will assume that the Supplier 1, Supplier 2, etc... cells are on row two and the column names are the same as the cell data (Supplier 1, Supplier 2, etc...). The month column name will be [Month Number] and the year column will be [Year Number]. Obviously you will need to adjust these particulars. Then use

.

=SUMIFS({Total Defects}, {Supplier}, @cell = [Supplier 1]$2, {Date}, AND(MONTH(@cell) = $[Month Number]@row, YEAR(@cell) = $[Year Number]@row))

.

{Total Defects}: Use the appropriate cross sheet referencing steps to select the new column we added to the master sheet called [Total Defects].

{Supplier}: Same as above except select the Supplier column in the master sheet.

{Date}: Same as above except select the Date column from the master sheet.

.

Using this and being sure to lock the appropriate column/row references in the formula will allow you to dragfill the formula to populate the rest of the summary table instead of having to manually adjust each formula.

Stephanie Stoewe

What did you use for {Date} in the reference sheet? Did you select both Month and date Columns?

Paul Newcome

The Inspection Data column.

Stephanie Stoewe

The summary sheet has all Supplier names on Row 15. 

The reference sheet has a new Total Defects column. 

 

I don't think I typed out the column numbers correctly in the formula, so here are all of the headings so we can make sure we use the right language in the formula.

 

Thank you so much for your help, by the way!

Paul Newcome

=SUMIFS({Total Defects}, {Supplier}, @cell = [Supplier 1]$15, {Date}, AND(MONTH(@cell) = $[Month #]@row, YEAR(@cell) = $[Year #]@row))

 

Give this a try. What steps are you taking to create the cross sheet references?

Stephanie Stoewe

It is telling me #Unparsed, which is odd - I've got the right sheet and the right columns linked from the reference sheet. 

Every time I use the {Column I select From Reference Sheet}. 

Paul Newcome

Are you able to post a published link the both sheets so I can look at them directly?