How to marry two working countif formulas?

[Guest]

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

 

[Guest]

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

 

[Ron Coderre]

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)

 

[RagDyeR]

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!
=====================================================

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

 

[RagDyeR]

Typo in the second date.

Use Max's!
--

Regards,

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

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!
=====================================================

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

 

[Ron Coderre]

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)

 

[Guest]

Try this:

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

 

[Guest]

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]

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

 

[Guest]

Thanks again Ron, that does make sense.

Have a great day!

Redwing ML