Calculating Totals By Quarter/Employees

[Casp]

Hello,

The formula:

=IFS(U1=1,SUMIFS(sold,solddates,">"&W1,solddates,"<="&X1,employee,employeeselect))

- Is being used to calculate sales by quarter and employee when selecting a radio button.
- W1 and X1 have the start/end date range for said quarter.
- When selecting a specific employee from a dropdown list, the sum result is correct and works as expected.
- In the same list, the top option is "All Employees".

Question:
How can I add onto this formula to see the total sold for that quarter if the option "All Employees" is selected in the dropdown list?

 

[steve the fish]

I would add an if test and use one sumifs without employee if 'all' was selected and the one you have if a particular employee was selected.

 

[Casp]

I would add an if test and use one sumifs without employee if 'all' was selected and the one you have if a particular employee was selected.

Is this what you mean? U1=1 is being used to determine the quarters so not sure my syntax is lining up to get the result.

=IF(U1=1,SUMIFS(sold,solddates,">"&W1,solddates,"<="&X1),SUMIFS(sold,solddates,">"&W1,solddates,"<="&X1,employee,employeeselect))

 

[Casp]

Is there anyone who can shed some light on this? Thanks!

 

[Casp]

Bump.

 

[Peter_SSs]

Try this

=IFS(U1=1,SUMIFS(sold,solddates,">"&W1,solddates,"<="&X1,employee,IF(employeeselect="All Employees","?*",employeeselect)))

 

[Casp]

Hi Peter, thank you for the formula. I was able to figure out the formula below to get the result I needed with some minor changes. Appreciate your help.

=IFS(U1=1,SUMIFS(sold,department,"*",employee,IF(employeeselect="All Employees","*",employeeselect),solddates,">"&W1,solddates,"<="&X1)

 

[Peter_SSs]

Glad you have it resolved. Thanks for letting us know.