How to marry two working countif formulas?
 

Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Try:
=SUMPRODUCT((B3:B50="ARNECOM")*(C3:C50=DATE(2006, 1,1))*(C3:C50<DATE(2006,2,1)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Redwing ML" wrote:
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

You'd need SUMPRODUCT for that:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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


"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Try this:

=SUMPRODUCT((B3:B50="ARNECOM")*(C3:C50=DATE(2006, 1,1)*(C3:C50<=DATE(2006,1,1))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Typo in the second date.

Use Max's!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Try this:

=SUMPRODUCT((B3:B50="ARNECOM")*(C3:C50=DATE(2006, 1,1)*(C3:C50<=DATE(2006,1,1))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Yikes! Typo...
This
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Should be:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1))

(I removed the last "=" from the formula)

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

Regards,

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

"Ron Coderre" wrote in message
...
You'd need SUMPRODUCT for that:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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


"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Try this:

=SUMPRODUCT((B3:B50="ARNECOM")*(TEXT(C3:C50,"mmm-yy")="Jan-06"))


"Redwing ML" wrote:

Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Thanks Ron and Team!

This seems to work just fine!

However, how come I had to use "SUMPRODUCT" instead of my original "COUNTIF"
or mybe even "IF"("THEN".....?? or "IF"("COUNTIF"...etc..etc..?

Redwing ML



"Ron Coderre" wrote:

Yikes! Typo...
This
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Should be:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1))

(I removed the last "=" from the formula)

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

Regards,

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

"Ron Coderre" wrote in message
...
You'd need SUMPRODUCT for that:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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


"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in two
different cells. How can I merge them together to count only if they meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

The date range COUNTIF ultimately returns one value.. Consequently, there's
no way to correlate the count of items within the date range with the number
of cells that ="ARNECOM". You really need to test each date/field_value
pair individually and accumulate the results. In those multiple-criteria
circumstances, SUMPRODUCT is usually most appropriate and least complicated
approach.

Regards,

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


"R
edwing ML" wrote in message
...
Thanks Ron and Team!

This seems to work just fine!

However, how come I had to use "SUMPRODUCT" instead of my original
"COUNTIF"
or mybe even "IF"("THEN".....?? or "IF"("COUNTIF"...etc..etc..?

Redwing ML



"Ron Coderre" wrote:

Yikes! Typo...
This
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Should be:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1))

(I removed the last "=" from the formula)

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

Regards,

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

"Ron Coderre" wrote in message
...
You'd need SUMPRODUCT for that:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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


"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in
two
different cells. How can I merge them together to count only if they
meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML

How to marry two working countif formulas?
 

Thanks again Ron, that does make sense.

Have a great day!

Redwing ML



"Ron Coderre" wrote:

The date range COUNTIF ultimately returns one value.. Consequently, there's
no way to correlate the count of items within the date range with the number
of cells that ="ARNECOM". You really need to test each date/field_value
pair individually and accumulate the results. In those multiple-criteria
circumstances, SUMPRODUCT is usually most appropriate and least complicated
approach.

Regards,

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


"R
edwing ML" wrote in message
...
Thanks Ron and Team!

This seems to work just fine!

However, how come I had to use "SUMPRODUCT" instead of my original
"COUNTIF"
or mybe even "IF"("THEN".....?? or "IF"("COUNTIF"...etc..etc..?

Redwing ML



"Ron Coderre" wrote:

Yikes! Typo...
This
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Should be:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<DATE(2006,2,1))

(I removed the last "=" from the formula)

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

Regards,

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

"Ron Coderre" wrote in message
...
You'd need SUMPRODUCT for that:
=SUMPRODUCT(--(B3:B50="ARNECOM"),--(C3:C50=DATE(2006,1,1)),--(C3:C50<=DATE(2006,2,1))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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


"Redwing ML" wrote in message
...
Hello Team,

I have two countif formulas that work perfectly for my spreedsheet in
two
different cells. How can I merge them together to count only if they
meet
both formulas and enter result in a third cell?

The formulas are as follows;

=COUNTIF(B3:B50,"ARNECOM")
=COUNTIF(C3:C50,"="&DATE(2006,1,1))-COUNTIF(C3:C50,"="&DATE(2006,2,1))

Hey, I thank you in advance!
Redwing ML