SUMIFS formula

Keith

=SUMIFS({Completed date}, MONTH(@cell) = [Jan 2018]$1, {S/S}, AND({Rep} = "Paul"))

 

Hi All,

Trying to get the above formula to work.

Referencing from another sheet. Want to be able to match a sales reps figures per month.

From these columns in the other sheet: Completed date column, Invoice value column and rep column.

So essentially the above formula is trying to pull the sales figures for Paul for Jan 2018.

I've got a formula that will give me sales figures for the whole company for Jan 2018 but now need figures by Rep.

Below is formula that works for company sales figures:

=SUMIF({Completed date range}, MONTH(@cell) = [Jan 2018]$1, {S/S value})

Thanks.

Chris McKay

Hi Keith,

I can't actually work out why your company sales figures formula works. Because it shouldn't. SUMIF/SUMIFS work like this:

=SUMIF({What you want to sum}, {the range you want to match criteria against}, "The criteria you want to match")

Your working formula has things around the wrong way (criteria before range) and it strikes me that you're looking to SUM {S/S Value}, not {Completed date range}.

SUMIFS also don't like dates very much, so normally you'd have to put in a workaround formula.

How are your dates formatted? Are they just in MM/DD/YY format and you're looking to match everything with the MM/YY that you have in {Completed date range}?

Assuming this is the case, you could try:

=SUMIFS({S/S Value}, {Completed date range}, LEFT(@cell, 2) + RIGHT(@cell, 2) = 1718, {Rep}, "Paul")

Kind regards,

Chris McKay