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

[GD]

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?

 

[Bob Phillips]

=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]

Sorry I should point out the original data posted was jumbled up in pasting,
i've removed some irrelevant columns to simplify things - i'm looking at a
total figure for GD (KT) IF it corresponds to January '08 (XX/01/01) - cheers

 

[GD]

Hi, cheers - unfortunately this is producing a #VALUE! readout?

 

[Bob Phillips]

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]

Ok, apologies for this. The data as it's presented on the sheet 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]

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]

Yeh, having sat and looked at how the formula is made up and referencing it
to my spreadsheet I can't see why it won't work, it's strange..
Yes, in between 3, 13, 23 are aesthetic filler fields, shaded cells etc,
they are the specific cells i'd be looking to concentrate on...
As I type I've remembered I have a hidden value in the column for use of a
filter, which once removed has made the formula work! Obviously it was
confusing the issue - is there a way of altering to concentrate just on the
3, 13, 23 etc etc cells or is it only available over an entire range?

Thanks though, that's spot on

 

[GD]

Also out of interest were I to want to run a similar function but simpler in
as much as it would be, If Column I was 'GD (KT)' and Column J was 'Phone'
then represent that with a cumulative figure based on frequency of occurance?
and miss out occurances of GD (KT) (in column I) with anything but 'Phone'
(in column J)

You'll have to bear with me on this learning curve...

 

[Bob Phillips]

You could try this array formula

=SUM(IF(MOD(ROW('2008'!$A$3:$A$260),10)=3,IF((YEAR('2008'!$A$3:$A$260)=2008)*(MONTH('2008'!$A$3:$A$260)=1)*
(LEN('2008'!I3:I260)-LEN(SUBSTITUTE('2008'!I3:I260,"GD (KT)",""))>0),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


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

 

[Bob Phillips]

I guess that you mean

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

although if I contains just GD (KT), that is not say xx GD (KT) xx, you can
simplify it to

=SUMPRODUCT(--('2008'!I3:I260="GD (KT)"),--('2008'!J3:J260="Phone"))

--
---
HTH

Bob


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