summing text with two conditions from two columns

  • Thread starter Thread starter Joker Poker
  • Start date Start date
J

Joker Poker

If somebody can help:
I have to summ (in cell K1) number of "Material Releases" from column "I" if
I have a letter "Y" in column "J".

Also apply condition to color cell if this cell contain a text "PSV", so I
can summ it from the column by the reading colors.
Second criteria would be if I have one color in a cell then my next reading
shfor different text should not apply a new color.
Thank you.
 
For your first query, try this:

=SUMPRODUCT((I1:I1000="Material Releases")*(J1:J1000="Y")*(K1:K1000))

I've assumed you have 1000 rows of data - adjust if necessary. I've
also assumed that you want to sum from column K - it's a bit confusing
in your post.

You can't use colours to determine what to sum in a formula. You will
need some VBA to do that.

Hope this helps.

Pete
 
Hi Pete_UK,
Let me explain again, sorry for confusing you:
In Column "I" I have to read a text like:PSV; Leak; Spill,etc...
In Column "J" I have letters "Y" or "N", so my conditions will be:
IF I HAVE IN COLUMN "I" letter "PSV" and in COLUMN "J" letter "Y" then summ
me total in column "K1" for example.
So, once I am going to apply colors to these cells I would like to read it
in Cell K1, how much was total of PSV
in CellK2, how much was total of Leak, etc...
I havehope this time I explan well.
Thanks
 
Do you mean, then, that you want to COUNT the number of times PSV
occurs in column I AND at the same time column J contains a "Y"? (Note
that sum implies adding some numbers together to get a total).

If this is the case, then you can put this formula in K1:

=SUMPRODUCT((I1:I1000="PSV")*(J1:J1000="Y"))

If you wanted a count for Leak when column J ="Y", then you could put
this in K2:

=SUMPRODUCT((I1:I1000="Leak")*(J1:J1000="Y"))

and so on for your other categories.

However, it would be better to put PSV in K1, LEAK in K2 etc, and then
in L1 you could have ths formula:

=SUMPRODUCT((I$1:I$1000=K1)*(J$1:J$1000="Y"))

Then you can just copy this down column L for as many categories that
you have in column K.

Hope this helps.

Pete
 
Hi Pete_UK,
Let me explain again, sorry for confusing you:
In Column "I" I have to read a text like:PSV; Leak; Spill,etc...
In Column "J" I have letters "Y" or "N", so my conditions will be:
IF I HAVE IN COLUMN "I" letter "PSV" and in COLUMN "J" letter "Y" then summ
me total in column "K1" for example.
So, once I am going to apply colors to these cells I would like to read it
in Cell K1, how much was total of PSV
in CellK2, how much was total of Leak, etc...
I havehope this time I explan well.
Thanks
--
Dan









- Show quoted text -
Click to expand...

Joker,

Your description is still a little vague.

Have you considered using a Pivot Table? It sounds like your data
would suit one.

I'm not sure what you want to achieve with your colour system, but, as
Pete said you cant sum using colours unless you use VBA.

Regards

David
 
Hi Dan,

In order to count the number lines that have "PSV" in column I AND "Y" in
column J for the following:

I J K
Description Yes or No PSV total: 2
PSV Y Leak total: 2
Leak Y Spill total: 3
Spill Y
PSV N
Leak N
Spill Y
PSV Y
Leak Y
Spill Y

enter into cell K1:

="PSV total: "&SUM(IF((I:I="PSV")*(J:J="Y"),1))

this is an array formula, so after keying in the formula into the cell, hold
down the SHIFT+CTRL+ENTER keys - when done correctly, you will see curly
brackets around the formula.

Hope this helps.
 
Back
Top