Complicated counting

[Guest]

Column A contains dates. Column B contains text. Worksheet is sorted by column A. I need to count the rows where A is a date in January, and B matches one of a list of text values (the list is in another column or sheet). Then I need to do the same thing for Feb, Mar etc

I'm struggling with this, so any help would be much appreciated

 

[Bob Phillips]

Hi Richard,

It's the ubiquitous SUMPRODUCT

=SUMPRODUCT((TEXT(A1:A100,"mmm")="Jan")*(B1:B100="value1"))

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Column A contains dates. Column B contains text. Worksheet is sorted by

column A. I need to count the rows where A is a date in January, and B
matches one of a list of text values (the list is in another column or
sheet). Then I need to do the same thing for Feb, Mar etc.

 

[Bob Phillips]

Richard,

re-reading it you probably want to lookup the value against the list, so try
this instead

=SUMPRODUCT((NOT(ISBLANK(A1:A100)))*(TEXT(A1:A100,"mmm")="Jan")*(NOT(ISNA(MA
TCH(B1:B100,Sheet2!A1:A10,0)))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, I will study that and see if I can make it work!

 

[Guest]

Bob

Thanks, I've nearly got it working now. The (main) remaining difficulty is that MATCH seems to work the wrong way round - my lookup array has wildcards in it, not my lookup value. For example
a row contains 01/01/04 "DIVE JAN1" and I want a match on a lookup array containing values like *JAN1*, *JAN2*

Richard

 

[Bob Phillips]

Richard,

That is almost impossible. How do you decide whether to lookup DIV or JAN1?
And what purpose do the wildcards in the lookup table serve, wildcards would
normally be used in the lookup value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,

Thanks, I've nearly got it working now. The (main) remaining difficulty is

that MATCH seems to work the wrong way round - my lookup array has wildcards
in it, not my lookup value. For example,

a row contains 01/01/04 "DIVE JAN1" and I want a match on a lookup array

containing values like *JAN1*, *JAN2*.