Sumproduct count not working

[kratos1992]

I want to count the number of cells that aren't blank in a staffing allocation report that I'm trying to create.

In a different tab I've created an exec summary that sums up based on certain criteria.

The count formula im using is:

=SUMPRODUCT(--('Main Report'!$F$7:$GF$7=43890)*(--('Main Report'!$B$7:$B$4754="Worker 1"))*(--('Main Report'!$F$8:$GF$8="FY19")*(--(LEN('Main Report'!$F$7:$GF$4754)>0))))

The above formula works perfectly flawlessly

The second formula I'm trying to create needs to be able to count all the non-blank cells based on fiscal, quarter and position. The formula is:

=SUMPRODUCT((--('Main Report'!$B$7:$B$4754="Worker 1"))*(--('Main Report'!$F$8:$GF$8="FY19")*(--('Main Report'!$F$9:$AF$9="Q4"))*(--(LEN('Main Report'!$F$7:$GF$4754)>0))))

I can't get the formula to work no matter what variation/ cell range I choose. I keep getting #N/A errors.

Does anyone have any ideas what I could do to fix this or even if its possible?

[FlameRetired]

Hello kratos1992. Welcome to the forum.

Try changing this part 'Main Report'!$B$7:$B$153=$B7 to this 'Main Report'!$B$10:$B$153=$B7

[kratos1992]

Hello, so I figured it out, it was because in my second formula the bolded range was not consistent with the other ranges

=SUMPRODUCT((--('Main Report'!$B$7:$B$4754="Worker 1"))*(--('Main Report'!$F$8:$GF$8="FY19")*(--('Main Report'!$F$9:$AF$9="Q4"))*(--(LEN('Main Report'!$F$7:$GF$4754)>0))))

[FlameRetired]

Good to hear. Thank you for letting us know.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.