Re: $$Excel-Macros$$ Need Help with IF(AND formula

2011-11-23 Thread Jedi Spencee 
Thank you!

On Tue, Nov 22, 2011 at 10:30 AM, Jedi Spencee wrote:

> Hello- I am trying to write a formula that would return the COUNT and
> SUM, with 3 criteria elements.
>
> IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
> D:D, = Oct-11),count, "0")
>
>
> Can this be done?
>
> Thank you.
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 






Erin

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Need Help with IF(AND formula

2011-11-22 Thread Asa Rossoff 
P.S.
Oh - for taking the sum with sumproduct, just add the sum range as an
additional argument to SUMPRODUCT (the order of arguments doesn't matter --
they are all multiplied by each other).

If your values to sum are in column X:

=SUMPRODUCT(--(data!F:F="A",--(data!B:B="04 - Closed Won"),--(data!
D:D=DATE(2011,10,1)),data!X:X)

For SUMIFS, the range to sum is the first argument, so just use:

=SUMIFS(data!X:X,data!F:F,"A",data!B:B,"04 - Closed
Won",data!D:D,DATE(2011,10,1))

Note that it is more efficient to specify the actual data range, not the
whole columns as you have.

For more on the SUMPRODUCT usage described, check out these links:
http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

COUNTIFS and SUMIFS are still pretty versatile, and fast, so check the help
for more info on how the arguments work.

Asa

-Original Message-
From: Asa Rossoff [mailto:a...@lovetour.info] 
Sent: Tuesday, November 22, 2011 7:38 PM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Need Help with IF(AND formula

If you are using Excel 2007+, use COUNTIFS for count and SUMIFS for sum.
If using an older Excel, use SUMPRODUCT for either.

IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
D:D, = Oct-11),count, "0")

=COUNTIFS(data!F:F,"A",data!B:B,"04 - Closed Won",data!D:D,DATE(2011,10,1))

I think does what you are trying to do... This is the equivalent SUMPRODUCT:

=SUMPRODUCT(--(data!F:F="A",--(data!B:B="04 - Closed Won"),--(data!
D:D=DATE(2011,10,1)))

SUMPRODUCT is more versatile of the two, but also more cryptic when used for
these purposes.  COUNTIFS and SUMIFS are much faster.

Read the help for COUNTIFS/SUMIFS if you have a newer Excel.

By the way, your use of AND() is incorrect.  Excel evaluates formulas from
the inside out -- not the same as human language.  Look for the most nested
parenthesis and resolve that part of the formula in your mind first, then
look at the next nesting level outside of that... repeat until you've
resolved everything.

If you realize that, then you'lll see that the "and" in AND(data!F:F = "A")
won't do anything.. there is only one thing inside the parenthesis.  AND
works with a list or array of things and returns TRUE if all of them are
true, otherwise false.



-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Jedi Spencee
Sent: Tuesday, November 22, 2011 9:30 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Need Help with IF(AND formula

Hello- I am trying to write a formula that would return the COUNT and
SUM, with 3 criteria elements.

IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
D:D, = Oct-11),count, "0")


Can this be done?

Thank you.

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Need Help with IF(AND formula

2011-11-22 Thread Asa Rossoff 
If you are using Excel 2007+, use COUNTIFS for count and SUMIFS for sum.
If using an older Excel, use SUMPRODUCT for either.

IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
D:D, = Oct-11),count, "0")

=COUNTIFS(data!F:F,"A",data!B:B,"04 - Closed Won",data!D:D,DATE(2011,10,1))

I think does what you are trying to do... This is the equivalent SUMPRODUCT:

=SUMPRODUCT(--(data!F:F="A",--(data!B:B="04 - Closed Won"),--(data!
D:D=DATE(2011,10,1)))

SUMPRODUCT is more versatile of the two, but also more cryptic when used for
these purposes.  COUNTIFS and SUMIFS are much faster.

Read the help for COUNTIFS/SUMIFS if you have a newer Excel.

By the way, your use of AND() is incorrect.  Excel evaluates formulas from
the inside out -- not the same as human language.  Look for the most nested
parenthesis and resolve that part of the formula in your mind first, then
look at the next nesting level outside of that... repeat until you've
resolved everything.

If you realize that, then you'lll see that the "and" in AND(data!F:F = "A")
won't do anything.. there is only one thing inside the parenthesis.  AND
works with a list or array of things and returns TRUE if all of them are
true, otherwise false.



-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Jedi Spencee
Sent: Tuesday, November 22, 2011 9:30 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Need Help with IF(AND formula

Hello- I am trying to write a formula that would return the COUNT and
SUM, with 3 criteria elements.

IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
D:D, = Oct-11),count, "0")


Can this be done?

Thank you.

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need Help with IF(AND formula

2011-11-22 Thread NOORAIN ANSARI 
You can also try...

=SUMPRODUCT((data!F:F = "A")*(data!B:B="04 - Closed
Won")*(data!D:D="Oct-11")*(data!K:K=0))

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Tue, Nov 22, 2011 at 11:00 PM, Jedi Spencee wrote:

> Hello- I am trying to write a formula that would return the COUNT and
> SUM, with 3 criteria elements.
>
> IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
> D:D, = Oct-11),count, "0")
>
>
> Can this be done?
>
> Thank you.
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need Help with IF(AND formula

2011-11-22 Thread Aindril De 
Hi,

If you are using Excel 2007 or 2010, then you can use the SUMIFS or
COUNTIFS to achieve the same.

Cheers
Andy

On Tue, Nov 22, 2011 at 11:00 PM, Jedi Spencee wrote:

> Hello- I am trying to write a formula that would return the COUNT and
> SUM, with 3 criteria elements.
>
> IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
> D:D, = Oct-11),count, "0")
>
>
> Can this be done?
>
> Thank you.
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 

Warm Regards,
Aindril De
Ph: 9811300157

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Need Help with IF(AND formula

2011-11-22 Thread Jedi Spencee 
Hello- I am trying to write a formula that would return the COUNT and
SUM, with 3 criteria elements.

IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data!
D:D, = Oct-11),count, "0")


Can this be done?

Thank you.

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com