LinkBack URL
About LinkBacks
Dear Experts,
Please find the attached excel sheet here, there i have put a formula to calculate the particular amount based on the code, but its not working , So-
can any one give an ideas based on Sum & Index & match.
Thnx
Sum index
Dear Experts,
Please find the attached excel sheet here, there i have put a formula to calculate the particular amount based on the code, but its not working , So-
can any one give an ideas based on Sum & Index & match.
Thnx
You could do it this way:
=SUM(SUMIFS(C:C,A:A,J6,B:B,E7),SUMIFS(C:C,A:A,J7,B:B,E7),SUMIFS(C:C,A:A,J8,B:B,E7))
or
=SUM(SUMIFS(C:C,A:A,{"B","D","F"},B:B,E7))
Last edited by Cutter; 06-24-2012 at 04:28 PM. Reason: Added alternative
@ Cutter...Can u give an ideas based on Sum-Index-Match ?
I can give this alternative:
=SUMPRODUCT((ISNUMBER(MATCH(A2:A36,J6:J8,0)))*(B2:B36=E7),C2:C36)
Or this array formula:
=SUM(IF(ISNUMBER(MATCH(A2:A36,J6:J8,0))*(B2:B36=E7),C2:C36))
i could not understand the fixation with SUM/INDEX/MATCH? what is the reason? i hope it is not some "homework" requirement... i dislike doing someone else's homework.
see if this satisfies your requirement:
Cell F7:
=SUM(INDEX((--(ISNUMBER(MATCH(A2:A36,J6:J8,0))))*(B2:B36=E7)*(C2:C36),0))
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
@Icestationzbra.....Great Thnku
@Cutter .....Thnk u
you did not say why the specific need for SUM / INDEX / MATCH!
@Ice....
M really sorry for the late reply, we have very large data base in the related xcl sheet, and when we use SUMPRODUCT OR SUMIFS it takes much time to give answer while make small changes,
because of this reason only, we have selected the SUM INDEX and MATCH.
rGRDS.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks