I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

Hi Frank

Try Sumproduct
=SUMPRODUCT(($A$1:$A$100=DATE(2006,11,13))*($B$1:$ B$100="Model"))
Change the date value and "Model" to suit.

You may be looking for lust the Year, as opposed to an individual date,
in which case I would do the following.

Place your "Model" in cells e.g. in cell D2:D10
and place your Dates in cells E1:H1 e.g 1/1/2000, 1/1/2001, 1/1/2002
then copy the following to E2 and copy down and across as appropriate
=SUMPRODUCT(($A$1:$A$100=E$1))*($B$1:$B$100=$D2))

Better still, create a Pivot Table.
For help on creating Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html


--
Regards

Roger Govier


"Frank P Florida" <Frank P wrote in
message ...
I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of
model
numbers. I want to create a row of the number of model number per
date. Any
help is appreciated

Thank you very much for your help this worked perfectly.

"Roger Govier" wrote:

Hi Frank

Try Sumproduct
=SUMPRODUCT(($A$1:$A$100=DATE(2006,11,13))*($B$1:$ B$100="Model"))
Change the date value and "Model" to suit.

You may be looking for lust the Year, as opposed to an individual date,
in which case I would do the following.

Place your "Model" in cells e.g. in cell D2:D10
and place your Dates in cells E1:H1 e.g 1/1/2000, 1/1/2001, 1/1/2002
then copy the following to E2 and copy down and across as appropriate
=SUMPRODUCT(($A$1:$A$100=E$1))*($B$1:$B$100=$D2))

Better still, create a Pivot Table.
For help on creating Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html


--
Regards

Roger Govier


"Frank P Florida" <Frank P wrote in
message ...
I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of
model
numbers. I want to create a row of the number of model number per
date. Any
help is appreciated

You're very welcome, Frank. Thanks for the feedback.

Did you take a look at Pivot Tables? You really should as they are a far
more effective way of quickly changing views of this type of data. Take
a little time to learn and understand them - that time will be well
rewarded.

--
Regards

Roger Govier


"Frank P Florida" wrote in
message ...
Thank you very much for your help this worked perfectly.

"Roger Govier" wrote:

Hi Frank

Try Sumproduct
=SUMPRODUCT(($A$1:$A$100=DATE(2006,11,13))*($B$1:$ B$100="Model"))
Change the date value and "Model" to suit.

You may be looking for lust the Year, as opposed to an individual
date,
in which case I would do the following.

Place your "Model" in cells e.g. in cell D2:D10
and place your Dates in cells E1:H1 e.g 1/1/2000, 1/1/2001, 1/1/2002
then copy the following to E2 and copy down and across as appropriate
=SUMPRODUCT(($A$1:$A$100=E$1))*($B$1:$B$100=$D2))

Better still, create a Pivot Table.
For help on creating Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html


--
Regards

Roger Govier


"Frank P Florida" <Frank P wrote
in
message ...
I want to be able to count the values in a list that meet two
different
criteria. There is one column of dates and then another column of
model
numbers. I want to create a row of the number of model number per
date. Any
help is appreciated

You can't use nested countifs. You can, however, use SUMPRODUCT for that
purpose. See here for some info: http://www.meadinkent.co.uk/xlsumproduct.htm

Dave


--
Brevity is the soul of wit.


"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

use this if you are doing it in a cell

=SUMPRODUCT(--(a1:a50="big"),--(b1:b50="fat"),--(c1:c50="ugly"))

it will tell you how many records there are which meet the criteria for big
and fat and ugly.

otherwise you might look at pivot tables as a neat way of viewing your data.
Drag the date field into the columns, and the model number (count of model
number) into the data section.
very quick pivot table training is he
http://www.datapigtechnologies.com/ExcelMain.htm


--
Allllen


"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

Frank I also have the same question...
This is the CountIF function I'm using but can't seem to get it to do what I
want.
In column B I'm counting the number of days between dates that are equal to
or greater than 365 days...that part works fine...but I also want to have the
countif only look at a specific persons name in column "W". Excel accepts
the format but does not return a correct answer...??

=COUNTIF(B$2:B$474,"=365""Sam Jones")

"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

=sumproduct(--(B$2:B$474-365),--(W$2:W$474="Sam Jones"))
--
Allllen


"41db14" wrote:

Frank I also have the same question...
This is the CountIF function I'm using but can't seem to get it to do what I
want.
In column B I'm counting the number of days between dates that are equal to
or greater than 365 days...that part works fine...but I also want to have the
countif only look at a specific persons name in column "W". Excel accepts
the format but does not return a correct answer...??

=COUNTIF(B$2:B$474,"=365""Sam Jones")

"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

sorry I should have written
=sumproduct(--(B$2:B$474=365),--(W$2:W$474="Sam Jones"))

--
Allllen


"Allllen" wrote:

=sumproduct(--(B$2:B$474-365),--(W$2:W$474="Sam Jones"))
--
Allllen


"41db14" wrote:

Frank I also have the same question...
This is the CountIF function I'm using but can't seem to get it to do what I
want.
In column B I'm counting the number of days between dates that are equal to
or greater than 365 days...that part works fine...but I also want to have the
countif only look at a specific persons name in column "W". Excel accepts
the format but does not return a correct answer...??

=COUNTIF(B$2:B$474,"=365""Sam Jones")

"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

Allllen, Your suggestion worked great!! Thank you very much
What are "--" ?? I have never used them.
Could not tell the difference between your first suggested fomula and the
second?


"Allllen" wrote:

sorry I should have written
=sumproduct(--(B$2:B$474=365),--(W$2:W$474="Sam Jones"))

--
Allllen


"Allllen" wrote:

=sumproduct(--(B$2:B$474-365),--(W$2:W$474="Sam Jones"))
--
Allllen


"41db14" wrote:

Frank I also have the same question...
This is the CountIF function I'm using but can't seem to get it to do what I
want.
In column B I'm counting the number of days between dates that are equal to
or greater than 365 days...that part works fine...but I also want to have the
countif only look at a specific persons name in column "W". Excel accepts
the format but does not return a correct answer...??

=COUNTIF(B$2:B$474,"=365""Sam Jones")

"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

The first had - instead of =

The double unary operator is covered here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


"41db14" wrote:

Allllen, Your suggestion worked great!! Thank you very much
What are "--" ?? I have never used them.
Could not tell the difference between your first suggested fomula and the
second?


"Allllen" wrote:

sorry I should have written
=sumproduct(--(B$2:B$474=365),--(W$2:W$474="Sam Jones"))

--
Allllen


"Allllen" wrote:

=sumproduct(--(B$2:B$474-365),--(W$2:W$474="Sam Jones"))
--
Allllen


"41db14" wrote:

Frank I also have the same question...
This is the CountIF function I'm using but can't seem to get it to do what I
want.
In column B I'm counting the number of days between dates that are equal to
or greater than 365 days...that part works fine...but I also want to have the
countif only look at a specific persons name in column "W". Excel accepts
the format but does not return a correct answer...??

=COUNTIF(B$2:B$474,"=365""Sam Jones")

"Frank P Florida" wrote:

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

Sure, I can help you with that! Nested COUNTIF statements can be very useful for counting values that meet multiple criteria. Here's how you can use them to count the number of model numbers per date:

1. First, select the cell where you want to display the count for the first date.
2. Type the following formula:
   Formula:

   =COUNTIF(range1,date)*COUNTIF(range2,model) 

3. Replace "range1" with the range of cells that contains the dates you want to count.
4. Replace "date" with the specific date you want to count. You can either type the date directly into the formula (e.g. "1/1/2022"), or you can reference a cell that contains the date (e.g. A2).
5. Replace "range2" with the range of cells that contains the model numbers you want to count.
6. Replace "model" with the specific model number you want to count. Again, you can either type the model number directly into the formula, or you can reference a cell that contains the model number.
7. Press Enter to complete the formula.

This formula will count the number of cells in the "range1" that match the "date" criteria, and then multiply that count by the number of cells in "range2" that match the "model" criteria. This will give you the total number of times that particular model number appears on that particular date.

You can then copy this formula to the other cells in the row to get the counts for the other dates. Just make sure to update the "date" criteria in each formula to match the date in the corresponding cell.