Sumifs

Hey,

I want sum the values in column C (R1) with next conditions:
1 - i canīt change the order of any variable,
2 - i have sum the values of column C (R1) following the conditions in column B (E2),
3 - and last condition, i only want sum last four values in column C (R1), for each variable of column B (E2) (for example: If i have the formula in cell D69 the output should be 2, if i have the formula in cell D70 the output should be 2, if i have the formula in cell D71 the output should be 2, and if i have the formula in cell D72 the output should be 0).
What i want is: in d2 a formula with this conditions, in d3 a formula with this conditions, in d4 a formula with this conditions, and so on. The reason to this form is automate the formula to calculate last 4 values of each variable in cell B2, B3, B4....
Itīs possible?
How can i do this?

Hi

Not sure if I can help but I will try. A few questions:

Is the data in column C always going to be binary (e.g., 1 or 0)?
Are the number of variables in column B constant (e.g., limited to the list you have provided m,n,o,p,q,r,s,t)?
Column A does not appear to be relevant. Is that true?
When you say you want only the last four values you mean the last four entries in Column C with a corresponding instance of each variable in Column B?
In the first several cells, there will only be 1 to 3 instances of each variable in the list. The farther you go down the list, you gain variable instances until you have 4. If there less than 4 instances of a variable what do you want to do?

Originally Posted by loginjmor

Hi

Not sure if I can help but I will try. A few questions:

Is the data in column C always going to be binary (e.g., 1 or 0)?
Are the number of variables in column B constant (e.g., limited to the list you have provided m,n,o,p,q,r,s,t)?
Column A does not appear to be relevant. Is that true?
When you say you want only the last four values you mean the last four entries in Column C with a corresponding instance of each variable in Column B?
In the first several cells, there will only be 1 to 3 instances of each variable in the list. The farther you go down the list, you gain variable instances until you have 4. If there less than 4 instances of a variable what do you want to do?

Hey,

Thanks to reply the post.
Well, i will answer to or questions (the friend kvsrinivasamurthy, gave the right answer, or one possible of the answer).
Yes , my data will going to be always binary. The number of variables in column B isnīt constant (this is a little sample of data). The column A in this case isnīt relevant. The answer to question "When you say.... ....in column B?" is yes. And the answer to last question is donīt do anything.

Originally Posted by kvsrinivasamurthy

Use this array formula in D2 (after pasting the formula press Ctrl+Shift+Enter, You will see the formula in flower bracket ) and drag down.

Please Login or Register  to view this content.

Hey thanks to replay the post.

First, the formula work well (perfect), thanks for that.
One last question. In the formula when you put "INDIRECT("C" & LARGE(IF", why you put the letter "C"? what is the reason?

Large(IF..... gives the row numbers. Indirect function with C decides the cell addresses.