Formula to count if in seperate columns

Topic

New Reply

Maybe somebody can help me:
Firstly i need to check which letter is in Column range A4:A200.
and THEN, IF it is letter B or C or M or Y . sum numbers in column B for letter in column A.

Like in attached you see example :
it will be A4 and A8 “b” letter sum = 2

After that when i will have usage sum of every letter i need to minus it from sum of “K” column same letter.

Thanks

Andrius

Reply | Quote

Replies

Andrius,

If I understand what you are asking, I suggest using the sumif function. To sum all the “b” in column A it would look like this: =SUMIF(A4:A200,”b”,B4:B200)

You can use the same function to find the sum of K: =SUMIF(G4:G200,”b”,K4:K200).

If you want to nest the functions, it would look like this: =(SUMIF(A4:A200,”b”,B4:B200))-(SUMIF(G4:G200,”b”,K4:K200))

Lastly, you can replace the b with a cell reference. If you are going to list the B, C, M, Y in separete cells to indicate which item you are calculating, then change the “b” to refer to the cell (eg N9).

Looking at the formula you have in your spreadsheet, I’m not sure what you want to do with the “colors”. As always you can use F4 or $ to lock the cell reference in the range.

Reply | Quote

I don’t follow exactly what you want, but it seems you may want a SUMIF:
=SUMIF($A$4:$A$10,”b”,$B$4:$B$10)

This sums the values in B4:B8 where A4:A8 is “b”.

Steve

Reply | Quote

Thanks mates thats what i am looking for.:rolleyes:

Reply | Quote

I found another problem.
You can imagine.

In column A is validation by list contains 17 different names.
and these 17 names have matches.

Like

printer 1 – Hp
Printer 2 – canon
printer 3 – hp
printer 4 – epson

What i wan to do.
If i choose in column A from drop box Printer 1 in column B it should show HP
if printer 2 canon.

I tried to do with IF(A2:A200 =”printer 1″, HP, IF(A2:A200 =”printer 3″, HP…..
but it allows only 7 IF’s

is it another way to do this?

Reply | Quote

Create a lookup table and use Vlookup to lookup the “printer x” and then get the corresponding name.

Steve

Reply | Quote