| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=if(and(... Question
Good Afternoon...
I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
Try something like this:
=SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
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'!D2:D500="Claims"),'2008'!J2:J500) or this: =SUMPRODUCT((MONTH('2008'!A2:A500)=1)*('2008'!D2:D 500="Claims")*'2008'!J2:J500) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... 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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
It was a typo (twice!).
Change =sumproducty() to =sumproduct() -- drop that Y character: =SUMPRODUCT(--(MONTH('2008'!A2:A500)=1), --('2008'!D2:D173="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'!D2:D173="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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html jcorle wrote: 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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
Yeah....I'm hvanig tbroule tpnyig tadoy. : \
Thnaks. noR "Dave Peterson" wrote in message ... It was a typo (twice!). Change =sumproducty() to =sumproduct() -- drop that Y character: =SUMPRODUCT(--(MONTH('2008'!A2:A500)=1), --('2008'!D2:D173="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'!D2:D173="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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html jcorle wrote: 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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
THANK YOU THANK YOU! WOOHOO! I have been struggling with this for 2 weeks!
"Ron Coderre" wrote: 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'!D2:D500="Claims"),'2008'!J2:J500) or this: =SUMPRODUCT((MONTH('2008'!A2:A500)=1)*('2008'!D2:D 500="Claims")*'2008'!J2:J500) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... 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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
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! "Dave Peterson" wrote: It was a typo (twice!). Change =sumproducty() to =sumproduct() -- drop that Y character: =SUMPRODUCT(--(MONTH('2008'!A2:A500)=1), --('2008'!D2:D173="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'!D2:D173="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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html jcorle wrote: 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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=if(and(... Question
You're very welcome....I'm glad I could help.
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... THANK YOU THANK YOU! WOOHOO! I have been struggling with this for 2 weeks! "Ron Coderre" wrote: 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'!D2:D500="Claims"),'2008'!J2:J500) or this: =SUMPRODUCT((MONTH('2008'!A2:A500)=1)*('2008'!D2:D 500="Claims")*'2008'!J2:J500) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... 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" wrote: Try something like this: =SUMPRODUCTY(--(MONTH('2008'!A2:A500)=1),--('2008'!D2:D173="Claims"),'2008'!J66:J82) or this: =SUMPRODUCTY((MONTH('2008'!A2:A500)=1)*('2008'!D2: D173="Claims")*'2008'!J66:J82) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "jcorle" wrote in message ... Good Afternoon... I have come up with this formula =IF(AND((MONTH('2008'!A2:A500)=1),('2008'!D2:D173= "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =IF(OR question | Excel Worksheet Functions | |||
| Question | New Users to Excel | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
| And Question, (a&b, or just a) | Excel Discussion (Misc queries) | |||
Linear Mode
