Re: A bit more help needed on an =SUMPRODUCT if possible?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

That data works fine for me with the formula I provided.

I notice you use row 3,13,23,33. What is in between these rows? Do you want
to ignore them?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"GD" <GD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:818A8CB8-979A-4C2D-B3EB-40D65673A6E8@xxxxxxxxxxxxxxxx
Ok, apologies for this. The data as it's presented on the *** is as
follows
A B C D E F G
H I
3 01/01/08 XXXX XXXX XXXX XXXX XXXX XXXX XXXX GD (KT)
13 01/01/08 XXXX XXXX XXXX XXXX XXXX XXXX XXXX AG (KT)
23 01/02/08 XXXX XXXX XXXX XXXX XXXX XXXX XXXX GD (KT)
33 01/01/08 XXXX XXXX XXXX XXXX XXXX XXXX XXXX GD (KT)
43 01/02/08 XXXX XXXX XXXX XXXX XXXX XXXX XXXX AG (KT)

Obviously fields with XXXX are populated with real data but it's
irrelevant,
the row numbers increase in increments of ten, and the columns are
correctly
represented with the letters above - so the formula desired would pick up
a
figure of 1 from the data above, with only one instance of GD (KT) in the
month of february

Cheers



Hopefuly this should
"Bob Phillips" wrote:

After this and your follow-up, I am not sure what is in what column.

Try posting the data with some cell character to show where they change,
and
column letters.

That formula works with the original data. The NG posting creating a
newline
between GD and (KT) which you need to tidy-up.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"GD" <GD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C3D2913-8D29-427C-B3D4-9F42DB5AC8D5@xxxxxxxxxxxxxxxx
Hi, cheers - unfortunately this is producing a #VALUE! readout?

"Bob Phillips" wrote:

=SUMPRODUCT(--(YEAR('2008'!$A$3:$A$260)=2008),--(MONTH('2008'!$A$3:$A$260)=2),--(LEN('2008'!I3:I260)-LEN(SUBSTITUTE('2008'!I3:I260,"GD
(KT)",""))>0))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"GD" <GD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1BE603B0-73F8-44FE-9968-9FE79EF454B0@xxxxxxxxxxxxxxxx
Hi, just before christmas I was helped out on sorting a formula that
would
produce a numerical value triggered by a certain text arrangement in
a
range
of cells - if possible i'd like to expand upon this?

If I put in an example below and the current formula i'm using

(Data)

02/01/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG)
GD
(KT)
02/01/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG)
AG
(KT)
02/01/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG)
SW2
(KT)
03/02/08 22/11/2007 21/12/2007 10:27 R-44622 484253 C89215 4A2 (BG)
GD
(KT)

(Formula)
=SUMPRODUCT(--(LEN('2008'!I3:'2008'!I260)-LEN(SUBSTITUTE('2008'!I3:'2008'!I260,"GD
(KT)",""))>0))

Ok, this formula is concentrating on the text in the 7th column, and
producing a cumulative total from the whole range for how many times
it
occurs...So for instance in that example the text 'GD KT' occurs
twice
so
the
formula produces a 2.

Now, what i'm looking for is introducing an IF value to this -
essentially
I'm aiming for a formula which will not only discern when a specific
text
appears in column 7, but ALSO only read out IF it corresponds to a
date
value
in Column A....at the moment the data in column A is day-specific,
which
could complicate things but I could amend this to a simple 01/08 if
needs
be
- in practical terms i'm trying to produce a total readout for
instances
of
GD (KT) and a separate monthly readout for analysis purposes...

Any ideas?








.

Quantcast