I want to use sumproduct to count multiple rows if the formula finds a match in a particular column. I'm using the following formula:

=SUMPRODUCT(('CPT Utilization'!C:C="IMRT Treatments Commercial")*('CPT Utilization'!AD:AO))+SUMPRODUCT(('CPT Utilization'!C:C="IMRT TX to Medicare plans (G Codes)")*('CPT Utilization'!AD:AO))+SUMPRODUCT(('CPT Utilization'!C:C="Daily Treatments Commercial Plans")*('CPT Utilization'!AD:AO))+SUMPRODUCT(('CPT Utilization'!C:C="Daily TX to Medicare plans (G codes)")*('CPT Utilization'!AD:AO))

It works fine, except currently there are no values for columns AE:AO, as those are future months, and they are displaying as #DIV/0, which is giving my formula total a #DIV/0. Is there a way to count the columns only when they have a total. So when AE has a 4, it will count it, but not if it is displaying #Div/0?

In a hidden cell, I've put a formula that will display the columns if a certain month is entered. For example, when February is typed it, the formula displays AD:AE. I tried linking that formula into my sumif, but it is pulling like this: ")*('CPT Utilization'!G15)).

G15 is on another sheet, so that is not working correctly.

  • If you don't mind.. you may accept the answer like this. It'll remove this question from SO 'unanswered' list. || Alternatively if the example doesn't work for you.. please share the details here. ( : – p._phidot_ Oct 11 '18 at 13:59

If you want the Count not the Sum you can use ISNUMBER like the following:

=SUMPRODUCT(('CPT Utilization'!C:C="IMRT Treatments Commercial")(ISNUMBER('CPT Utilization'!AD:AO)))+SUMPRODUCT(('CPT Utilization'!C:C="IMRT TX to Medicare plans (G Codes)")(ISNUMBER('CPT Utilization'!AD:AO)))+SUMPRODUCT(('CPT Utilization'!C:C="Daily Treatments Commercial Plans")(ISNUMBER('CPT Utilization'!AD:AO)))+SUMPRODUCT(('CPT Utilization'!C:C="Daily TX to Medicare plans (G codes)")(ISNUMBER('CPT Utilization'!AD:AO)))

In this case if in 'CPT Utilization'!AD:AO there is text or #Div/0 it will not be included.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.