• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUM(COUNTIF) Merge

paulcherianc

paulcherianc

Member
Guys!

Is there any way to combine the below formula and avoid repeating {"A","UV","PV","R","TR"} keeping mind that I need to keep each cell reference like N7, O7 and so on.

=SUM(COUNTIF(N7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF(O7,{"A","UV","PV","R","TR"}))

Can anyone suggest me a trick!
 
In this case, if I Cut and Paste column "O" to another range the formula won't work. This is why I need to keep the cell reference. Any thoughts!
 
This is how I manage it now ..which is bit heavy!

=IF(DAY(EOMONTH(Timesheet!$N$4,0))=31,SUM(COUNTIF($N7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($O7,{"A","UV","PV","R","TR"}))++SUM(COUNTIF($P7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Q7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($R7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($S7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($T7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($U7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($V7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($W7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($X7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Y7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Z7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AA7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AB7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AC7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AD7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AE7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AF7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AG7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AH7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AI7,{"A","UV","PV","R","TR"}))++SUM(COUNTIF($AJ7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AK7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AL7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AM7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AN7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AO7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AP7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AQ7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AR7,{"A","UV","PV","R","TR"})),IF(DAY(EOMONTH(Timesheet!$N$4,0))=30,SUM(COUNTIF($N7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($O7,{"A","UV","PV","R","TR"}))++SUM(COUNTIF($P7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Q7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($R7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($S7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($T7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($U7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($V7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($W7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($X7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Y7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Z7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AA7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AB7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AC7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AD7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AE7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AF7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AG7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AH7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AI7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AJ7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AK7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AL7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AM7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AN7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AO7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AP7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AQ7,{"A","UV","PV","R","TR"})),SUM(COUNTIF($N7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($O7,{"A","UV","PV","R","TR"}))++SUM(COUNTIF($P7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Q7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($R7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($S7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($T7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($U7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($V7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($W7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($X7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Y7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($Z7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AA7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AB7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AC7,

{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AD7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AE7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AF7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AG7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AH7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AI7,

{"A","UV","PV","R","TR"}))++SUM(COUNTIF($AJ7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AK7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AL7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AM7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AN7,{"A","UV","PV","R","TR"}))+SUM(COUNTIF($AO7,

{"A","UV","PV","R","TR"}))))
 
Try,

=SUMPRODUCT(0+ISNUMBER(MATCH($N7:OFFSET($AO7,0,LOOKUP(DAY(EOMONTH($N$4,0)),{28;30;31},{0;2;3})),{"A","UV","PV","R","TR"},0)))

Regards
Bosco
 
In this case, if I Cut and Paste column "O" to another range the formula won't work. This is why I need to keep the cell reference. Any thoughts!
Click to expand...

Yes - if you end up having to construct such cumbersome formulae, then it almost always means there are issues with the way you have structured your data. I think you are probably coming at this from the wrong angle: if you attach a sample file and explain your need to copy and paste, then someone may be able to offer a better way of structuring your data.
 
You must log in or register to reply here.
Back
Top