SUMIFS Formula

Clare C

Hi Team!

I am trying to work out a formula to sum the forecast amount column for a particular salesperson if the sales stage is "1 - Open" or "2 - Bid Submitted". I have tried SUMIFS formula but keep getting the inparseable error.

Please help!

Screenshot 2022-06-09 114415.png

Kelly Moore

Try this

=SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 1}, OR(@cell = "1 - Open", @cell = "2 - Bid Submitted"), {Sheet - Sales Pipeline VPC Range 1}, "Joe Prideaux")

Kelly Moore

Hey @Clare C

What formula were you using?

Here is one that hard codes the salesperson's name. Depending on how your data is arranged, you may be able to collect this dynamically. This formula assumes the SUMIFS formula is on the same sheet as the data. If the formula is on a different sheet, the data will use cross sheet references, - we would need to change the formula for cross references.

=SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], OR(@cell="1 - Open", @cell= "2 - Bid Submitted"), Salesperson:Salesperson, "Joe Prideaux")

Does this work for you?

Kelly

Clare C

Hi Kelly,

I am needing to reference another sheet so I have changed it in the formula you provided above and now it's giving me an invalid operation error.

=SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 1}, OR({Sheet - Sales Pipeline Range 1} = "1 - Open", {Sheet - Sales Pipeline Range 1} = "2 - Bid Submitted"), {Sheet - Sales Pipeline VPC Range 1}, "Joe Prideaux")

Thanks for your help.

Clare

Kelly Moore

Try this

=SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 1}, OR(@cell = "1 - Open", @cell = "2 - Bid Submitted"), {Sheet - Sales Pipeline VPC Range 1}, "Joe Prideaux")

Clare C

Oh my god thank you so much!!!