Prev: Excel 2003 -insufficient Resources
Next: How do I stop a Pivot Table from Sorting my data (Excel 2007)
From: Phippsy on 26 Apr 2010 09:56
Hi

I need a formula to count cells in one column containing dates depending on
the contents of a differnt column like SumIf has the option
From: Eduardo on 26 Apr 2010 10:06
hi
=COUNTIF(A2:A5,"South")



"Phippsy" wrote:

> Hi
>
> I need a formula to count cells in one column containing dates depending on
> the contents of a differnt column like SumIf has the option
From: Jacob Skaria on 26 Apr 2010 10:10
If you are looking to count the dates which falls on a certain month..try the
below formula which count the number of dates in ColA which falls in the same
month as the date specified in cell B1.

=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")=TEXT(B1,"mmyyyy")))

If this is not what you are looking for please elaborate with an example..

--
Jacob (MVP - Excel)


"Phippsy" wrote:

> Hi
>
> I need a formula to count cells in one column containing dates depending on
> the contents of a differnt column like SumIf has the option
From: Jacob Skaria on 26 Apr 2010 10:13
You can add more conditions as below....

ColA date = same month as in cell B1 and ColC = cell D1

=SUMPRODUCT((TEXT(A1:A100,"mmyyyy")=TEXT(B1,"mmyyyy"))*
(C1:C100=D1))

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

> If you are looking to count the dates which falls on a certain month..try the
> below formula which count the number of dates in ColA which falls in the same
> month as the date specified in cell B1.
>
> =SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")=TEXT(B1,"mmyyyy")))
>
> If this is not what you are looking for please elaborate with an example..
>
> --
> Jacob (MVP - Excel)
>
>
> "Phippsy" wrote:
>
> > Hi
> >
> > I need a formula to count cells in one column containing dates depending on
> > the contents of a differnt column like SumIf has the option
 | 
Pages: 1
Prev: Excel 2003 -insufficient Resources
Next: How do I stop a Pivot Table from Sorting my data (Excel 2007)