Sumif

[MadWoman]

The formula returns a 0. I need it to return $38,241.51

=SUMPRODUCT((Data!B1:B20000="ABC
Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a
0. I need it to return $38,241.51.

Column B = Company Name
Column Q = MM/YY
Column L = Subtotal dollar amount

What am I missing and how can this be corrected to return the correct value.

 

[Jim Thomlinson]

A couple of things... I column Q actual dates that have been formatted mm/yy
or is it text. If it is text then your criteria needs to be in the same
format and not "Apr-08". Secondly try using the unary operator -- to coerce
the true/false values to 1/0 values.

=SUMPRODUCT(--(Data!B1:B20000="ABC
Company"), --(Data!Q1:Q20000="Apr-08"), (L1:L20000))

if Q is actual dates then

=SUMPRODUCT(--(Data!B1:B20000="ABC
Company"), --(Month(Data!Q1:Q20000)=4), --(Year(Data!Q1:Q20000)=2008),
(L1:L20000))

 

[Roger Govier]

Hi

=SUMPRODUCT((Data!B1:B20000="ABC Company")*
(TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000))

 

[Andrea Jones]

You can use =SUMPRODUCT((B1:B20000="ABC Company")*(Q1:Q20000>=DATEVALUE("1
April 2008"))*(Q1:Q20000<=DATEVALUE("30 April 2008"))*(L1:L20000))

Andrea Jones
www.stratatraining.co.uk

 

[MadWoman]

OK, now I get a return error of #VALUE! Now what? PS. Column Q is a TEXT
field.

 

[MadWoman]

PS I'm using Excel 2002.

Hi

=SUMPRODUCT((Data!B1:B20000="ABC Company")*
(TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000))

 

[MadWoman]

I tried this and still get a return value of 0. PS I'm using Excel 2002.

 

[MadWoman]

Thanks for your help.

I originally entered the data with a ' in front of Apr-08 and have since
changed column Q to a text field. I still get 0. I tried changing column Q
to a date field as you suggested and still get 0 for a result.

I'm so confused.

 

[Roger Govier]

Hi

I missed a closing parenthesis in my formula.
It should have read
=SUMPRODUCT((Data!B1:B20000="ABC Company")*
(TEXT(Data!Q1:Q20000,"mmm-yy")="Apr-08")*(L1:L20000))

However, you say your dates are Text and not true Excel dates.
You also said in your original post that column C was formatted MM/YY which
would be 04/08

Perhaps you need
=SUMPRODUCT((Data!B1:B20000="ABC Company")*
(Data!Q1:Q20000="04/08")*(L1:L20000))

 

[MadWoman]

Roger: Thanks for taking the time to help me with this. I could still use
your help if you are willing. I've been thinking about it and have other
questions. I'm using SUMPRODUCT in other spreadsheets and it works well
for, of all things, COUNTING. That said, I don't understand how SUMPRODUCT
will sum up the Subtotaled $$ Figures in Column L.

I'm trying to get a formula together that if the two conditions are met,
that the COLUMN L value returned is the SUM of the two conditions, not a
count. I've been dinking w/ the formula editor & have changed Columns B & Q
to Text and made all my addition/multiplacation fields in the same format.

PS. I'm using Excel 2002.
Thanks again for your help and look forward to hearing from you.
Lost in New Mexico,
Madelyn.

 

[Roger Govier]

Hi Madelyn

Send me a copy of the file
To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.

 

[Roger Govier]

File received and returned.

there were a few entries in the date column that were Text entries, either
because they had been entered as text, or because there was some extra text
typed after the date itself.
Once the column of data was "cleaned", Sumproduct worked fine.

I added some named ranges of the form
=Data!$D$4:INDEX(Data!$B:$B,COUNTA(Data!$B:$B)+2)

and then the formula
=SUMPRODUCT((Vendor=$B4)*(MONTH(Invdate)=MONTH(H$3))*(Value))

Where H3:S3 contained dates for each of the 12 months that Madelyn required
the Summary for.

I would have suggested using a Pivot Table instead, but lots of blanks
within Invdate would have prevented grouping by Month.