countifs

[nadja]

Hi,
I'm trying to create a countif formula that takes into account 3 different criteria:

countif
a) column R equals "delivery" and
b) column S begins with 2 or begins with 8

So far I've come up with this "if" formula, but excel doesn't seem to like the "or" in it:
IF(OR('Adjustments Dec 2011'!S:S="2*",'Adjustments Dec 2011'!S:S="8*"),COUNTIF('Adjustments Dec 2011'!R:R,'Adjustments Dec 2011'!R:R="DELIVERY"))

I hope this makes sense. Any help is highly appreciated!

 

[Jonmo1]

Countif can only do 1 criteria.

You need sumproduct.
Try this

=SUMPRODUCT(--(R1:R1000="Delivery"),--((LEFT(S1:S1000,1)="2")+(LEFT(S1:S1000,1)="8")>0))

I left out the sheet references for simplicity


Important
You can't use Entire Column Refs like S:S in sumproduct
Unless in XL2007 or higher, even then it's not recommended.

 

[MrVillareal]

Hi,
I'm trying to create a countif formula that takes into account 3 different criteria:

countif
a) column R equals "delivery" and
b) column S begins with 2 or begins with 8

So far I've come up with this "if" formula, but excel doesn't seem to like the "or" in it:
IF(OR('Adjustments Dec 2011'!S:S="2*",'Adjustments Dec 2011'!S:S="8*"),COUNTIF('Adjustments Dec 2011'!R:R,'Adjustments Dec 2011'!R:R="DELIVERY"))

I hope this makes sense. Any help is highly appreciated!

Is column s all number or combination of text and numbers? what version of excel you have?

 

[barry houdini]

Hello nadja, try something like this

=SUMPRODUCT(('Adjustments Dec 2011'!R$2:R$100="Delivery")*(LEFT('Adjustments Dec 2011'!S$2:S$100)={"2","8"}))

adjust ranges as required

 

[CharlesChuckieCharles]

Can you not use COUNTIFS ?

Which allows multiple criteria

 

[nadja]

Is column s all number or combination of text and numbers? what version of excel you have?

I'm using Excel Professional Plus 2010. The cells in column s are combinations of numbers and text, but they all start with a number.

 

[MrVillareal]

I'm using Excel Professional Plus 2010. The cells in column s are combinations of numbers and text, but they all start with a number.

Have you tried the post above? They already have excellent solution.

 

[nadja]

Have you tried the post above? They already have excellent solution.

I've tried both the sumproduct formulas but only get result #n/a.

Has it maybe got to do witht he fact that some of the results in column s are #n/a?

 

[CharlesChuckieCharles]

IF you have 2010 why don't you use COUNTIFS with the multiple criteria

 

[Jonmo1]

I've tried both the sumproduct formulas but only get result #n/a.

Has it maybe got to do witht he fact that some of the results in column s are #n/a?

Yes, if there are #N/A errors within the referenced ranges, then the sumproduct will error as well.