sumif and the if formula problems

A

aly1cat

I am using this formula but not sure if I have it in the right context.
However, I have several worksheets of data and am trying to create
formula that will provide a sum total if the market segment = OS
(column v in formula) AND the date = Jan-04 (column D in formula)

=SUMIF(OSS!$V$2:$V$100="OSS",andif(OSS!$D$2:$D$100="Jan-04",OSS!O$2:O$100,0))

Can someone help
 
R

rvExcelTip

=SUM(IF((OSS!$V$2:$V$100="OSS")*(OSS!$D$2:$D$100="Jan-04"),OSS!O$2:O$100,0))

ctrl+shift+ente
 
F

Frank Kabel

Hi
try
=SUMPRODUCT((OSS!$V$2:$V$100="OSS")*(OSS!$D$2:$D$100="Jan-04"),OSS!O$2:
O$100)

though i'm not so sure if your column D stores this date as a text or
if you just have formated a date with the custom format MMM-YY. For the
latter one change the formula to
=SUMPRODUCT((OSS!$V$2:$V$100="OSS")*(MONTH(OSS!$D$2:$D$100)=1)*(YEAR(OS
S!$D$2:$D$100)=2004),OSS!O$2:O$100)
 
A

aly1cat

sorry, I am a bit lost...?????

1. the date fields are already formatted.

2. not sure why the multiplication? What I need to get is the figure
from (i.e column L) BUT ONLY IF it matches the criteria of a date o
Jan 04 and it is of that segment specified i.e(oss column v).

Thanks

The formula you gave is giving me an absurd figure
 
F

Frank Kabel

Hi
the second one should give you the correct figure (the multiplication
just multiplies boolean values for your conditions). Have you checked
the second one
If this does not work you may post (plain text please) some example
rows of your data
 
Top