SUMPRODUCT with 2 criteria from date and specific text

[chaiyya345]

Hi All,
I have a worksheet with SUMPRODUCT to get total of working days & it is working fine: =SUMPRODUCT(--(TEXT($C$4:$C$35,"MMM")=$N4)*($E$3:$L$3=O$3)*$E$4:$L$35)
Table 1: Data Source. Table 2: Data prep

With the additions to the above, I wish to get the SUMPRODUCT working to this current & with specific text "MC". I have tried to add another arguments to the formula but returns with #VALUE!.

Please find attachment.
Any help is greatly appreciated

Thank-You very much

[Sam Capricci]

deleted post, not sure I see what you want after all - the additional "MC" part.

[bebo021999]

Where is the "MC range"?
basically, add it to SUMPRODUCT:

=SUMPRODUCT(--(TEXT($C$4:$C$35,"MMM")=$N4)*($E$3:$L$3=O$3)*$E$4:$L$35*(MC range="MC"))

In case "MC" is patial string in, i.e, " xxx MC xxx"
using ISNUMBER(search("MC",MC range)) instead of (MC range="MC")

[chaiyya345]

Hi Sam,
Thanks for responding.
The specific text "MC" is where to make the SUMPRODUCT to calculate this to the current formula. the current formula is to calculate "1"
eg, there are 3 "MC" & "1"from EMP1 (Table 1; Data Source), making total of 6

Hope this answered.

Thank-You very much.

[chaiyya345]

Hi Bebo,
MC is not a partial text, its specific. The correct SUMPRODUCT formula should be able to calculate MC & "1", making total of 2 (1 for MC, 1 for "1")
I have tried your formula to O18, but it returns #VALUE!.

Thank-You very much

[bebo021999]

So, counting range is mixed of text and value, with "MC" equals 1?

If counting range contains only "MC" and 1:

Please Login or Register  to view this content.

Else, array formula is required:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[chaiyya345]

Hi Bebo,
just wow!!! both your solutions works!
1. pretty simple without ctr+shift+enter, if finds any text & numbers
2. ctr+shift+enter is required since its not SUMPRODUCT & the formula will look for a specific text for MC in that range.

Will mark SOLVED & close this thread.

Reputation added to bebo021999

Thank-You very much