I am totally baffled as to how to write a formula to do what i need. I think it might combine the if and sumproduct functions but I am having some problems figuring it out. I would like to automatically sum the number of projects that are Red, Yellow, or Green for each department and name and have the qty displayed in cells L15:N19. I would like to be able to add rows to the list (A:H) and have the equation automatically update (L15:N19) Please see attached file.
Last edited by statenja; 04-23-2008 at 11:43 PM.
Hi
Try
L15: =SUMPRODUCT(--($A$3:$A$10=$J15),--($B$3:$B$10=$K15)*(($D$3:$D$10="Red")+($E$3:$E$10="red")+($F$3:$F$10="Red")+($G$3:$G$10="red")+($H$3:$H$10="red")))
Copy down and across. Update the red for yellow and green as required.
HTH
rylo
Thanks, That worked great! I actually changed the titles in L14:N14 to "Red", "Yellow", and "Green" and updated the formula as follows so I didn't need to update after I copied it to all the cells.
=SUMPRODUCT(--($A$1:$A$1000=$J15),--($B$1:$B$1000=$K15)*(($D$1:$D$1000=L$14)+($E$1:$E$1000=L$14)+($F$1:$F$1000=L$14)+($G$1:$G$1000=L$14)+($H$1:$H$1000=L$14)))
Again, thank you for the help! that is a big time saver.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks