Use if function with sumproduct

[renren]

Hi,
I have been using a spreadsheet for a while that uses the following formula to count the amount of assessments are happening in a given month, the source data is spat out from a system so has lots of columns and rows:

=SUMPRODUCT((TEXT('Company Data'!S:S,"mm-yyyy")="07-2018")*1)

Now I want to use the same forumla but for only specfic people whose names appear in a seportate columm which is 'Company Data'!G:G'

I feel I should be adding a =if 'Company Data'!G:G'="john", SUMPRODUCT((TEXT('Company Data'!S:S,"mm-yyyy")="07-2018")*1), "") but this is totally wrong. nothing seems to work!

Any help would be amazing. Oh is an array formula

Thanks

 

[Joe4]

Just add another Condition to your Sumproduct, i.e.

[COLOR=#333333]=SUMPRODUCT((TEXT('Company Data'!S:S,"mm-yyyy")="07-2018")*([/COLOR][COLOR=#333333]'Company Data'!G:G'="john"[/COLOR][COLOR=#333333]))[/COLOR]

 

[Muhammad_Usman]

Hey, Try this one!

Book1
	R	S	T	U
2	John	7/10/2018		2
3		7/10/2018
4		7/10/2018
5	John	7/10/2018
6		7/10/2018
7		7/10/2018
8		7/10/2018
9		7/10/2018
10		7/10/2018
11		7/10/2018
Sheet5

Cell Formulas
Range	Formula
U2	=SUMPRODUCT((TEXT(S:S,"mm-yyyy")="07-2018")*1*(R:R="John"))

 

[renren]

Hey, Try this one!

	R	S	T	U
2	John	7/10/2018		2
3		7/10/2018
4		7/10/2018
5	John	7/10/2018
6		7/10/2018
7		7/10/2018
8		7/10/2018
9		7/10/2018
10		7/10/2018
11		7/10/2018

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula U2 =SUMPRODUCT((TEXT(S:S,"mm-yyyy")="07-2018")*1*(R:R="John")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks both for your replies, Muhammad's formula worked I think becuase of the *1* bit.
I was trying to add the new condition at the start rather than the end.

Thanks lots for your help!

 

[Joe4]

The "*1" is not necessary. That was just in there originally because you only had one condition, and had to coerce your TRUE values to numeric ones.
Since we are introducing a second condition that we are multiplying by, we no longer need the "*1".

The issue in my formula was an extra quote mark that I got when I copied it directly from your original post (after the G:G):

I feel I should be adding a =if 'Company Data'!G:G'="john", SUMPRODUCT((TEXT('Company Data'!S:S,"mm-yyyy")="07-2018")*1), "") but this is totally wrong. nothing seems to work!

Remove that, and my formula should work just fine:

[COLOR=#333333]=SUMPRODUCT((TEXT('Company Data'!S:S,"mm-yyyy")="07-2018")*([/COLOR][COLOR=#333333]'Company Data'!G:G="john"[/COLOR][COLOR=#333333]))[/COLOR]