IF AND or something else?

[LittleJazzinho]

Hi guys, I am currently trying to make a document for work and I am a little bit out of my depth. I'm hoping someone may be able to point me in the right direction or to tell me if what I am trying to do just isn't possible.

I have created a data profile which works fantastically well using pretty basic COUNTIF and IF formulae.
(I am going to find it very difficult to explain whats going wrong without being able to show you guys a picture, I'll try my best though).

My current formulae are:
=COUNTIF(J3:J34,"B")

I would like to expand the above formula to do this...
When C2 reads AUT, count "B" in J3:J34,
When C2 reads SPR, count "B" in J3:J34,
When C2 reads SUM, count "B" in J3:J34.

I would like it to do all these things in one cell (I hope I'm not asking the world). I also have =COUNTIFS(B3:B34, "M", J3:J34, "B") that I would like to apply the same formula to.

 

[JoeMo]

Maybe:

=IF(OR(C2="AUT,C2="SPR",C2="SUM"),COUNTIF(J3:J34,"B"),put here what you want if C2 is none of the three)

 

[hotabae]

My current formulae are:
=COUNTIF(J3:J34,"B")

I would like to expand the above formula to do this...
When C2 reads AUT, count "B" in J3:J34,
When C2 reads SPR, count "B" in J3:J34,
When C2 reads SUM, count "B" in J3:J34.

I also have =COUNTIFS(B3:B34, "M", J3:J34, "B") that I would like to apply the same formula to.

For the first Type:

=IF(OR(C2="AUT",C2="SPR",C2="SUM"),COUNTIF(J3:J34,"B"),"")

For the second Type:

=IF(OR(C2="AUT",C2="SPR",C2="SUM"),COUNTIFS(B3:B34, "M", J3:J34, "B"),"")

I have the cells displaying a blank entry if it is not one of the three cases (AUT, SPR, SUM), but you can put whatever you want in the "" argument.

Is this what you were looking for?

 

[LittleJazzinho]

I'm so sorry, I put the wrong information in the initial post I do apologise about that (thank you for replying so quickly btw!)
The correct columns are as follows:

When C2 reads AUT, count "B" in J3:J34,
When C2 reads SPR, count "B" in K3:K34,
When C2 reads SUM, count "B" in L3:L34

How would that alter your above formula? Thank you in advance.

 

[markmzz]

I'm so sorry, I put the wrong information in the initial post I do apologise about that (thank you for replying so quickly btw!)
The correct columns are as follows:

When C2 reads AUT, count "B" in J3:J34,
When C2 reads SPR, count "B" in K3:K34,
When C2 reads SUM, count "B" in L3:L34

How would that alter your above formula? Thank you in advance.

Maybe this:

=COUNTIF(INDEX($J$3:$L$34,,MATCH(C2,{"AUT";"SPR";"SUM"},0)),"B")

Markmzz

 

[JoeMo]

Try:

=IF(C2="AUT",COUNTIF(J3:J34,"B"),IFC2="SPR",COUNTIF(K3:K34,"B"),IF(C2="SUM",COUNTIF(L3:L34,"B"),put here what you want if C2 is none of the three)))

 

[markmzz]

Maybe this:

=COUNTIF(INDEX($J$3:$L$34,,MATCH(C2,{"AUT";"SPR";"SUM"},0)),"B")

Markmzz

Hi!

A small modification in the formula:

=IFERROR(COUNTIF(INDEX($J$3:$L$34,,MATCH(C2,{"AUT";"SPR";"SUM"},0)),"B"),"")

Markmzz