| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count if using columns not range of cells
Hi,
Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for the month of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
=SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jun08"),--(B1:B100="Apple"))
"Opal" wrote: Hi, Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for the month of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
=SUMPRODUCT(--(TEXT(A1:A100,"yyyymmm")="2008Jun"),--(B1:B100="Apple"))
-- __________________________________ HTH Bob "Opal" wrote in message ... Hi, Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for the month of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
One way, assuming data in cols A and B expected within rows 2 to 10
In say, C2, copied down: =IF(A2="","",SUMPRODUCT((TEXT(A$2:A$10,"mmmyy")="J un08")*(B$2:B$10=B2))) Adapt ranges to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Opal" wrote: Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for the month of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
Thanks so much "Teethless Mama"!
Only thing was my comp wasnt recognising ur commas. I had to change them to semicolons. Thanks again! -- Opal "Teethless mama" wrote: =SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jun08"),--(B1:B100="Apple")) "Opal" wrote: Hi, Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for the month of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
And what if you wanted those between April 15 and May 15?
"Bob Phillips" wrote: =SUMPRODUCT(--(TEXT(A1:A100,"yyyymmm")="2008Jun"),--(B1:B100="Apple")) -- __________________________________ HTH Bob "Opal" wrote in message ... Hi, Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for the month of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
=SUMPRODUCT(--(A1:A100=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple"))--__________________________________HTHBob"Bigfoot17 " wrote in ... And what if you wanted those between April 15 and May 15? "Bob Phillips" wrote: =SUMPRODUCT(--(TEXT(A1:A100,"yyyymmm")="2008Jun"),--(B1:B100="Apple")) -- __________________________________ HTH Bob "Opal" wrote in message ... Hi, Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for themonth of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal
|
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if using columns not range of cells
Whoa! What happened there
=SUMPRODUCT(--(A1:A100=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple")) -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A1:A100=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple"))--__________________________________HTHBob"Bigfoot17 " wrote in ... And what if you wanted those between April 15 and May 15? "Bob Phillips" wrote: =SUMPRODUCT(--(TEXT(A1:A100,"yyyymmm")="2008Jun"),--(B1:B100="Apple")) -- __________________________________ HTH Bob "Opal" wrote in message ... Hi, Im trying to generate a count that will tell me how many of 'x' are in column B only if column A's data are within a date range eg for themonth of June. A B 16-Jun-08 Apple 16-Jun-08 Pear 30-May-08 Apple 18-Jun-08 Orange 16-Jun-08 Apple 12-Apr-08 Pear Thanks -- Opal |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Certain Cells in Specified Range | Excel Worksheet Functions | |||
| count cells in a range | Excel Worksheet Functions | |||
| count all the columns in a range with certain conditions | Excel Discussion (Misc queries) | |||
| Count occurances in range of cells | Excel Worksheet Functions | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Linear Mode
