Excel query problem --- PLEASE help!

  • Thread starter Thread starter webfangler
  • Start date Start date
W

webfangler

HELP!

'Membership Data'!AD1:AD4998 (Contains the variable
"06","07","08","09",10","C1","C2","C3")

'Membership Data'!CG1:CG4998 (Contains the variable dates in th
following format dd/mm/yyy)

'Membership Data'!CN1:CN4998 (Contains the variables "A","C")

What I need to know is... How many records are there where CN="A" an
AD="06" and CG contains the month dd/01/yyyy.

Please, Please, please..... how the hell do i do this?!?

Thanks in advance
Steve Le
 
Web,
A simple fix would be to set up the range and autofilter
it using the criteria you listed below. then using the
formula subtotal(2,AD1:AD4998) to count all visible cells
in that region.
 
'Membership Data'!AD1:AD4998 (Contains the variable
"06","07","08","09",10","C1","C2","C3")

'Membership Data'!CG1:CG4998 (Contains the variable dates in th
following format dd/mm/yyy)

'Membership Data'!CN1:CN4998 (Contains the variables "A","C")

What I need to know is... How many records are there where CN="A" an
AD="06" and CG contains the month dd/01/yyyy.

or use in cell zz1
=concantenate(ad1,month(cg1),cn1)
(then all the way down to zz4998
then countif(zz1:zz4889,"0601A)

or similar
if you want to cut out all but 20 or so records, you can email it to m
and i will put in some formulas....? (e-mail address removed)
(remove clothes)

http://www.buffyslay.co.u
 
Hi!

I guess Frank will be along soon with a SUMPRODUCT.

Meanwhile:

you can extract the month from the date using =MONTH(CG1),if you are i
the first row.

The rest you've almost done. =AND(p,q,r) will give a value TRUE i
all of p,q,r are true (things like AD1="06" or CN1="A"), otherwise i
gives FALSE.

String it together and the formula
=IF((AND(MONTH(CG1)=6,AD1="06",CN1="A")=TRUE,1,"")) will show 1 if al
three conditions are true and blank ("") if not. (You don't need th
=TRUE bit to make it work, but it might help to make clear what i
going on).

Put this in row 1 of an empty column (say DA). Copy it down to ro
4998.

There will be a 1 alongside each row which satisfies your conditions.
Just need to count the ones. Put in cell DA4999 =SUM(DA1:DA4998) o
else =COUNT(DA1:DA4998)

Al
 
Thanks LOADS for your prompt responses... I have to leave the office no
but I'll be trying out all of your suggestions when I return. I'll le
you know how I got on.

Regards
Steve Le
 
buffyslay

Thanks for the offer but thres loads of sensitive information in th
spreadsheet so I canot send it to you... but thanks a bunch for you
offer... very generous.

Regards
Steve Le
 
Back
Top