[mso] Re: Question regarding IF Statements in Excel
- From: Thomas Hutchins <hutch99999@xxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Wed, 8 Apr 2009 15:15:56 -0700 (PDT)
Nuts. I see DATEVALUE got changed to ÚTEVALUE again. Another way you can do
this is with an array formula:
{=SUM(IF(A1:A13="Repair",IF(C1:C13<ÚTEVALUE("2/26/2008"),B1:B13,0)))}
An array formula must be entered by pressing CTRL+Shift+Enter and not just
Enter. If you do it correctly then Excel will put curly brackets around the
formula {}. You can't type these yourself. If you edit the formula you must
enter it again with CTRL+Shift+Enter.
If posting changes DATEVALUE again, you'll have to change it back when you
enter it in your worksheet.
Hutch
--- On Wed, 4/8/09, hutch99999@xxxxxxxxx <hutch99999@xxxxxxxxx> wrote:
From: hutch99999@xxxxxxxxx <hutch99999@xxxxxxxxx>
Subject: [mso] Re: Question regarding IF Statements in Excel
To: mso@xxxxxxxxxxxxx
Date: Wednesday, April 8, 2009, 4:17 PM
I don't know how UTE got in there. That part is supposed to be the DATEVALUE
function. Leave the "--" characters; they are needed. The formula (hoping it
posts correctly) is:
=SUMPRODUCT(--(A1:A500="repair"),--(C1:C500<ÚTEVALUE("2/26/2008")),B1:B500)
Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Hutch
--- On Wed, 4/8/09, Kelly Arnold <Kelly.Arnold@xxxxxxxxxxxxxxxx> wrote:
From: Kelly Arnold <Kelly.Arnold@xxxxxxxxxxxxxxxx>
Subject: [mso] Re: Question regarding IF Statements in Excel
To: mso@xxxxxxxxxxxxx
Date: Wednesday, April 8, 2009, 3:19 PM
Hutch,
This is not working for me.
I've removed the "--" and the "UTE" from the formula when placing in the
spreadsheet. It's coming back with a "-" value. That at least tells me I'm
going in the right direction, I'm just not getting the numbers I need.
Any further suggestions?
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf Of
Thomas Hutchins
Sent: Wednesday, April 08, 2009 1:40 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Question regarding IF Statements in Excel
This worked for me (I used columns A, B, and C) for your columns 1, 2, and 3:
=SUMPRODUCT(--(A1:A500="repair"),--(C1:C500<ÚTEVALUE("2/26/2008")),B1:B500)
Hope this helps,
Hutch
--- On Wed, 4/8/09, Kelly Arnold <Kelly.Arnold@xxxxxxxxxxxxxxxx> wrote:
From: Kelly Arnold <Kelly.Arnold@xxxxxxxxxxxxxxxx>
Subject: [mso] Re: Question regarding IF Statements in Excel
To: mso@xxxxxxxxxxxxx
Date: Wednesday, April 8, 2009, 11:20 AM
I would like to add totals from one column based on criteria from two
others.
See the sample from the spreadsheet below:
ATM
$ 410.00
02/08/08
repair
$ 730.92
02/08/08
repair
$ 275.49
01/24/08
repair
$ 1,697.82
01/29/08
repair
$ 718.16
02/08/08
repair
$ 375.00
01/29/08
capex
$ 18,475.00
02/08/08
repair
$ 522.25
02/01/08
ATM
$ 147.00
02/08/08
ATM
$ 365.00
02/08/08
repair
$ 1,554.04
02/08/08
repair
$ 2,279.00
02/22/08
ATM
$ 104.00
02/08/08
I want to add the dollars in column 2 if the data in column 3 is greater
than my date criteria AND the information in column 1 says "repair"
I have the SUMIF formula for the date range needed, but it of course
adds all of the numbers.
Formula: =SUMIF($N11:$N500,"<=2/26/08",$J11:$J500). This returns a total
of $27,653.68.
I need the formula to return only the items that are labeled as
"repair". This would then return a total of $8,152.68.
Spreadsheet manipulation is not easy because it is used by a community,
not just me. I want to imbed my formulas to pull the information I need
for reporting purposes.
Thanks for responding, Kelly
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of David Smart
Sent: Wednesday, April 08, 2009 9:56 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Question regarding IF Statements in Excel
There are ways to do tests on grouped data. It depends on the test.
Please provide an example of exactly what you want to do.
Regards, Dave S
----- Original Message -----
From: "Kelly Arnold" <Kelly.Arnold@xxxxxxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Wednesday, April 08, 2009 11:09 PM
Subject: [mso] Question regarding IF Statements in Excel
> Hello!
>
>
> Is there a way to create an IF statement using a range of cells or can
> it only be done on a cell by cell basis?
>
>
>
> I want to be able to establish the criteria from a range and then add
a
> different range (based on the first criteria range) for a total. What
I
> am looking for is different than the SUMIF statement.
>
>
>
> Thanks,
>
> Kelly
>
>
>
>
> *************************************************************
> You are receiving this mail because you subscribed to
mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your
email
> address or change your email settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo
sister
> group. This group will not allow for posting of emails, but will
allow
> you to join and share problem files, templates, etc.:
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to
fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top. This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow
you to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
FILE SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your
email messages, read these instructions that explain why and how to fix
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you to
join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you to
join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you to
join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister
group. This group will not allow for posting of emails, but will allow you to
join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
