Logical suming rules

[aly1cat]

=SUM(IF(OSS!$D$2:$D$98,"Jan-04",OSS!P$2$98))+AND(IF(OSS!$V2:$V$98,"OSS"))

I am having trouble getting this formula correct. I am trying write
formula that basically looks for information in two colums and if bot
rules are correct then it will look in another column for the value an
so on.
i.e my formula is asking if the workbook "oss" in column d has an
dates showing Jan-04, if so check the corresponding row and columnan
if it contains "oss" then sum the figure in the row in column "p".

I think this makes sense!

 

[Frank Kabel]

Hi
are your dates in column D real dates (thats is a date value formated
as MMM-DD) If yes try the following
=SUMPRODUCT((MONTH(OSS!$D$2:$D$98)=1)*(YEAR(OSS!$D$2:$D$98)=2004)*(OSS!
$V$2:$V$98="OSS"),OSS!$P$2:$P$98)

 

[Arvi Laanemets]

Hi

I'd prefer
=SUMPRODUCT((MONTH(OSS!$D$2:$D$98)=1)*(YEAR(OSS!$D$2:$D$98)=2004)*(OSS!$V$2:
$V$98="OSS")*(OSS!$P$2:$P$98))

 

[Bob Phillips]

Arvi,

Can I ask why? The advantage of using a comma for the values part is that it
differentiates it, and it is consistent with the standard use of SUMPRODUCT.

--

HTH

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

 

[Frank Kabel]

Hi Arvi
why do you prefer this? (just curious). The benefits of separating the
sum column as a second parameter are
- According to sum discussions in this NG this may be a little bit
faster
- If this column contains text (e.g. a heading row) this won't throw an
error - though your alternative would