IF Statements in multiple columns

[clstathos]

I am using excel 2010 and looking to use IF statements to add multiple columns that have both letters and numbers. I have come as far as to get all the coding in so that when one of each condition is presented they total correctly the problem I am having is if there is more than one of the same condition. For example the IF statement I am using is =IF(ISNA(MATCH("1P",C7:CO7,0)),0,1)+IF(ISNA(MATCH("2P",C7:CO7,0)),0,2) and so on. Obviously between cells C7 and CO7 there are many cells and if more than one cell has 1P or 2P in it the additional cells are not being added and only one. How can I get my formula to recognize the condition in more than one cell?

Thanks

[jeffreybrown]

Hello & Welcome to the Forum,

Are you adding or just trying to count the number of 1P and/or 2P?

=SUM(COUNTIF(C7:CO7,{"1P","2P"}))

[clstathos]

I am adding so if the cells have 1P, 1P, 2P the total should be 4...the way I have it I would get 3 (only one 1P is added).

Thanks

[Cutter]

I would say you need

=SUM(COUNTIF(C7:CO7,"1P"),COUNTIF(C7:CO7,"2P")*2)

[clstathos]

Thanks Cutter, but that isn't working I don't need it to just multiply by two I need the formula to could any within those range of cells.

Thanks

[Cutter]

I think you need to upload a small sample file (personal data removed/changed) to show various typical data and expected results.

[jeffreybrown]

What do you get for 1P, 1P, 2P, and 2P? Maybe 6

Also, other than 1P and 2P, is there anything else in these cells we should know about?

[Cutter]

@Jeff

It's the "and so on" part of the first post that has me wanting a sample file.

[clstathos]

Cutter the "and so on" is that it keeps going 1P, 2P, 3P, 4P, 5P, 6P, 7P, 8P I have it to add it there is only one of each but the multiples, they don't like me

Thanks again for all your help! I really appreciate it

[clstathos]

Alright guys here is a sample of what I am trying to do B7 should add up to 20 but it is only giving me 10.

Thanks

[Cutter]

If the expected result for that line is 20 then this array formula works (for single digit P's)

=SUM(IF(MID(C7:AG7,2,1)="P",LEFT(C7:AG7,1)*1)) confirmed with Ctrl+Shift+Enter

How high does the "and so on" go???

[Cutter]

An alternative would be expanding my first suggestion:

=SUM(COUNTIF(C7:AG7,"1P"),COUNTIF(C7:AG7,"2P")*2,COUNTIF(C7:AG7,"3P")*3,COUNTIF(C7:AG7,"4P")*4,COUNTIF(C7:AG7,"5P")*5,COUNTIF(C7:AG7,"6P")*6,COUNTIF(C7:AG7,"7P")*7,COUNTIF(C7:AG7,"8P")*8)

or (and I like this best)

=SUM(COUNTIF(C7:AG7,{"1P","2P","3P","4P","5P","6P","7P","8P"})*{1,2,3,4,5,6,7,8})

[Haseeb Avarakkan]

You can also shorten Cutter's formula to,

=SUM(COUNTIF(C7:AG7,{1,2,3,4,5,6,7,8}&"P")*{1,2,3,4,5,6,7,8})

[Cutter]

And now I like that one best.

If we shorten it any more it'll just be =SUM("")

[Haseeb Avarakkan]

...when looking your other formulas, there are other values like "E", "M", "J", "B". If you want to include all thses, try

=SUM(COUNTIF(C7:AG7,{1,2,3,4,5,6,7,8}&{"P";"E";"M";"J";"B"})*{1,2,3,4,5,6,7,8})

Or,

this Array Formula (Array Formula must be confirmed with CTRL+SHIFT+ENTER, rather tahn just ENTER) will SUM ALL the starting numbers, regardless of "P", "E" etc...

=SUM(IFERROR(LEFT(C7:AG7,{1;2;3})+0,0))

[clstathos]

Sorry to just be getting back to you guys This one worked great: =SUM(COUNTIF(C7:AG7,{1,2,3,4,5,6,7,8}&"P")*{1,2,3,4,5,6,7,8}.

Thank you all for your help!

[FDibbins]

could the 1P, 2P etc be put into a column that the OP could use a vlookup on, guys?

[Cutter]

We've lost our OP.