Hi All,
I want to add formula in G4 to add values from cell range D4:D24
where Text of C4:C24 matches with G3 and
Number of cell summed should be upto C1.(Means if c1 is 4 should add first 4 values only)
Please see Image for explanation.
tt.JPG
Hi All,
I want to add formula in G4 to add values from cell range D4:D24
where Text of C4:C24 matches with G3 and
Number of cell summed should be upto C1.(Means if c1 is 4 should add first 4 values only)
Please see Image for explanation.
tt.JPG
Last edited by Ashurk77; 01-30-2014 at 08:16 AM.
=SUM(IF(OFFSET($B$4,0,,SMALL(IF($B$4:$B$24=G$3,ROW($C$4:$C$24)-ROW($B$4)+1),$C$1))=G$3,OFFSET($B$4,0,1,SMALL(IF($B$4:$B$24=G$3,ROW($C$4:$C$24)-ROW($B$4)+1),$C$1))))
Confirm COntrl+Shift+enter
If you are happy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thanks
Robert
but it is giving wrong output,
Before that I was using this
=SUMIF(AI5:OFFSET($AI5,6*($K$2-1),,,),BJ4,$AK$5:$AK$700)
where AI is Area list
K2 is count number
Bj4 is Area to be matched
and AK contain Values
But now I want to add Count condition as well.
Please see attached
Not sure about the count.
Coudl you elaborate?add.xlsx
Yes it is working fine here but some how on my original data is is not working right.
Can we do something on my above posted formula?
Attached your workign file.
WHy my formual does not work?
pLEASE SEE MY eXCEL
Last edited by Ashurk77; 01-30-2014 at 12:17 PM.
This setup is very different from the one on your jpg.
Could you specify your outcome and where the criteria cells are?
No this is same situation just the cell numbers are changed.
here K2 is passing number of cells to be add.
and AI is area name Ak is cell value to be added.
and in BJ and BK i want output.
t3.xls
Please see attached
Hello Sir,
Still answer is not matching what I am looking for.
Might be I am missing some words to explain it fully.
File attach contain the answer I am looking for Please see cell number BJ 10,11,12 n 14
Value should be this only.
For counting purpose we can use Week Cell also(AJ) we will add upto 3 weeks only.(I dont know weather this week will be helpfull or not)
as AK2 is 3 If AK2 is 4 we will add upto 4 weeks.
Irrespective weather occurrence come or not.
Which file?
Please see
http://www.sendspace.com/file/52bz92
Last edited by Ashurk77; 01-31-2014 at 10:33 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks