Ask a new question

Using =SIMIF Question

I've been using

=SUMIF(Forecast!C12:C75,E25,Forecast!D12:D75) (this formula is used to find either Tip Waged or Regular Pay Employees)

for a while now and have never had a problem until recently. Or rather, never noticed it until now.

 

When using the formula above, it Matches a person's name from E25 to C12:C75 and Inserts their Pay Rate from D12:D75 into the current cell that holds the formula.

 

My problem is that if that person in E25 are listed twice (which they need to be, they have two pay rates), it adds the pay rates together.

 

Within the same sheet, I use the formula

=SUMIFS(Forecast!D12:D75,Forecast!C12:C75,E11,Forecast!D12:D75,">="&Forecast!S8)
to find only people with the pay rate that is > or = to S8 which is 7.25. This works fine.

 

The formula in question =SUMIF needs to be able to find both people listed as Tipped Wage or Regular Pay employees.

 

Any Idea how to make this work?

 

 

... The formula in question =SUMIF needs to be able to find both people listed as Tipped Wage or Regular Pay employees ...


A SUMIFS() function can be duplicated with the older SUMPRODUCT() function and can provide a little more flexibility. For example, your formula,
=SUMIFS(Forecast!D12:D75,Forecast!C12:C75,E11,Forecast!D12:D75,">="&Forecast!S8)
... can also be written as,
=SUMPRODUCT((Forecast!D12:D75)*(Forecast!C12:C75=E11)*(Forecast!D12:D75>=Forecast!S8))

SUMPRODUCT() can also be used to produce the equivalent of an OR() condition like this,
=SUMPRODUCT((Forecast!D12:D75)*(Forecast!C12:C75=E11)*(Forecast!D12:D75>=Forecast!S8)*((Forecast!Z12:Z75="Tipped Wage")+(Forecast!Z12:Z75="Regular Pay")))
... where Forecast!Z12:Z75 holds the type of employment.

If this close to what you were looking for?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

With this formula

 

=SUMPRODUCT((Forecast!D12:D75)*(Forecast!C12:C75=E25)*(Forecast!D12:D75>=Forecast!S8))

 

I get the #VALUE error.

 

The SUMPRODUCT I don't believe is needed for what I am looking for. May be doing a little too much for what I needed.

Thanks everyone who has given me answers to my questions!
If I could put everyone's name, I would!

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

... I get the #VALUE error ...


The #VALUE! error is likely due to a text value in Forecast!D12:D75. SUMPRODUCT() does not skip over text entries when performing mathematical operations on a column of numbers the way SUM(), SUMIF() and SUMIFS() do. Comparative operations do not produce this error (e.g. Forecast!Z12:Z75="Regular Pay") as the result is a 1 or 0., but you cannot attempt to add 1 + "a" + 2 in SUMPRODUCT().

Can you identify a non-number in Forecast!D12:D75?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Yes there are. I have each section for Dayshift and Nightshift separatd. So there is Text Between the two.

Here is a link to the file I am using if you want to take a look-see. I am not sure where I went wrong at.

When I used
=SUMIFS(Forecast!D12:D75,Forecast!C12:C75,E11,Forecast!D12:D75,">="&Forecast!S8)
that you gave me, it seems to not be playing well with other formulas, or I messed up somehting I am not sure where.

 

On The Workbook:

 

On the Creator sheet, under Monday, Sarah is showing in two positions (which will never happen in actual circumstances), one is Tip Waged and the other is Regular Pay. Unfortunately they are some how being added together instead of it finding one or the other. This is where I messed up at I believe.

Forecast is where the information if inserted for the employees,

Carhops are Tip Waged at 4.75 and every other Position is set at Regular Wage of 7.25.

Anyone who works for Tips cannot work in any position but Carhop. Anyone who makes Regular Wage can work any position, including Carhop.

 

This is what I am trying to accomplish in this thread.


https://skydrive.live.com/redir.aspx?cid=0eca664f63bb3979&resid=ECA664F63BB3979!477&parid=ECA664F63BB3979!125&authkey=!ALsxuCyli-wc-kM

Thanks everyone who has given me answers to my questions!
If I could put everyone's name, I would!

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

... On the Creator sheet, under Monday, Sarah is showing in two positions (which will never happen in actual circumstances), one is Tip Waged and the other is Regular Pay ...


Sarah was nowhere to be found so I put her in two positions and, yes, the wages were added together. The Creator worksheet showed the two wages totaled. But this is an error on the Forecast sheet, not the Creator sheet. This doesn't even have to be a single employee in two positions. Someone could put in a new entry for an existing employee and produce the same error. You need to catch the error on the Forecast worksheet.

Select Forecast!D22:D46 and create a CF based on a formula with the formula being,
=COUNTIF($C$22:$C22,$C22)>1
... then select Font, Strikethrough as the format. This will strikethrough the wage entry of any duplicate employee making the error visible to the user and allowing them to deal with it immediately. It doesn't stop it from being totaled on the Creator worksheet, but the problem should be dealt with here in any event.

Another solution is to use VLOOOKUP() on the Creator worksheet to catch only the first matching entry. Creator!J8's formula would be,
=IFERROR(VLOOKUP(E9,Forecast!$C$22:$D$46,2,FALSE),0)
... and that can be Copy, Paste Special, Formulas down the column so as to not to disturb the formatting. This is not the most advisable method as it leaves an error on the Forecast page and may result in an incorrect wage rate.

As I see it, the best procedure is to identify a duplicate entry on the Forecast page and have it dealt with there. There are any number of ways to do this, including locking out a duplicate entry. I've provided a less aggressive approach above.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I like the idea of using

=COUNTIF($C$22:$C22,$C22)>1

with the format abive to identify the problem before it happens.

 

Unfortunately I failed to notice that I didn't state that some employees are required to have two seperat pay rates. One Regular, and one Tip Wage. Some days when they work as Carhop, they are paid one rate, then when they work a regular position, they are paid Regular pay. So some will have their names listed twice with two different rates.

Is it possible to prevent them from adding together at all?

I know a simple solution would be to list the employee as Sarah and Sarah C for carhop. I've done this before, but do not like having a C beside the employee's name unless I just happen to have two employees named Sarah which I would have to put the first letter of her last name so that both know who is working on what position.

Thanks everyone who has given me answers to my questions!
If I could put everyone's name, I would!

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 905 Applies to: