=if(and(... Question

[jcorle]

Good Afternoon...

I have come up with this formul
=IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2173="Claims")),SUM('2008'!J66:J82),0)

However, the formula only brings up "$0.00" instead of the 17 million or so
it should upon proper calculation... HELP!

Thanks ahead of time!
Jen

 

[Ron Coderre]

Try something like this:

=SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2173="Claims"),'2008'!J66:J82)

or this:
=SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2173="Claims")*'2008'!J66:J82)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[jcorle]

with the SumproductY it says #Name? without the Y it says #Value! for the
first one and #N/A? for the second one.

THanks, though.

 

[Ron Coderre]

Yikes! Typos....(my fingers must be due for a tune-up!)

ALSO...The referenced ranges must all be the SAME SIZE...like this:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),--('2008'!D2500="Claims"),'2008'!J2:J500)

or this:
=SUMPRODUCT((MONTH('2008'!A2:A500)=1)*('2008'!D2500="Claims")*'2008'!J2:J500)

Does that help?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Dave Peterson]

It was a typo (twice!).

Change =sumproducty() to =sumproduct() -- drop that Y character:

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),
--('2008'!D2173="Claims"),
'2008'!J66:J82)

And if A2:A500 could be empty, you'll want to check to see if there's a
number/date in A2:A500.

=SUMPRODUCT(--(MONTH('2008'!A2:A500)=1),
--(isnumber('2008'!a2:a500)),
--('2008'!D2173="Claims"),
'2008'!J66:J82)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Ron Coderre]

Yeah....I'm hvanig tbroule tpnyig tadoy. : \
Thnaks.

noR

 

[jcorle]

THANK YOU THANK YOU! WOOHOO! I have been struggling with this for 2 weeks!

 

[jcorle]

Thank you so much for your help - I'll keep the links for future use... I
never heard of the Sumproduct formula last week... I can't say I'm fond of
it.... LOL!

 

[Ron Coderre]

You're very welcome....I'm glad I could help.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)