sumif with the criterion of what to sum in a different cell

[essta]

Thank you so much for the opportunity to ask questions of the experts (I hope my Q wont be too primitive ). I am summing a column of numbers, using sumif, to only sum the pay of the hourly workers. I typed in the function to do that, and believe it or not it works. But I need the user to be able to choose what to sum by clicking a cell that specifies the criterion of what to sum eg. hourly or salaried. If I rewrite the function each time replacing the criterion, it works, but it won't do. If the choice (criteria) of what they want totaled is located in some cells outside the table, I need to bring that choice into the function. It does work if I use a cell reference ie j4 instead of "hourly" but I still need the j4 or j5 to get into the function.Thank you so much!Essta

 

[kweaver]

Could you post some sample data and expected results?

 

[DanteAmor]

Welcome to the forum!
Also put here the formula used.

 

[essta]

Welcome to the forum!
Also put here the formula used.

Thank you & thank you for responding!
IF(G6="Salaried",SUMIF(C2:C12,G6,E2:E12),SUMIF(C2:C12,"Hourly",E2:E12))
or just:
=SUMIF(Status,G6,Pay) But this is not really what I need. I need it to either add "hourly" or "salaried" depending on what the user clicked. So there would be two cells outside the table, one saying "Salaried" the other "hourly". If g6 is clicked then sum the hourly, if g7, the salaried. Is code needed for that?
Thank you very much!
essta

 

[kweaver]

If I understand you correctly, you could put a simple drop-down in G6. Then, in H6, for example (or wherever you want the sum), you could do this:

=SUMPRODUCT((G6=C2:C11)*E2:E11)

 

[essta]

Thank you....not sure how to do that, even after reading about inserting a screenshot? I think i got it working. I used a separate cell with data validation, so the user only clicks on the criterion needed. The sumif function refers to that cell and sums whatever the contents of that cell is, Hourly or Salaried. the problem with that is that I have to have the cells with the values for them to pick from place elsewhere on the sheet...so is there any other solution?
I use excel2016, windows 10.
Thank you so much.
Essta

 

[DanteAmor]

Thank you & thank you for responding!
IF(G6="Salaried",SUMIF(C2:C12,G6,E2:E12),SUMIF(C2:C12,"Hourly",E2:E12))
or just:
=SUMIF(Status,G6,Pay) But this is not really what I need. I need it to either add "hourly" or "salaried" depending on what the user clicked. So there would be two cells outside the table, one saying "Salaried" the other "hourly". If g6 is clicked then sum the hourly, if g7, the salaried. Is code needed for that?
Thank you very much!
essta

In G6 you must put the value "Salaried" or "Hourly"

Then

=SUMIF(C2:C12,G6,E2:E12)

 

[DanteAmor]

Thank you....not sure how to do that, even after reading about inserting a screenshot? I think i got it working. I used a separate cell with data validation, so the user only clicks on the criterion needed. The sumif function refers to that cell and sums whatever the contents of that cell is, Hourly or Salaried. the problem with that is that I have to have the cells with the values for them to pick from place elsewhere on the sheet...so is there any other solution?
I use excel2016, windows 10.
Thank you so much.
Essta

Try this
Sheet1 contains the data, in sheet2 the formula

=SUMIF(sheet1!C2:C12,sheet2!G6,sheet1!E2:E12)

 

[essta]

If I understand you correctly, you could put a simple drop-down in G6. Then, in H6, for example (or wherever you want the sum), you could do this:

=SUMPRODUCT((G6=C2:C11)*E2:E11)

Wow!! that worked!! Thank you so much!! But I'm not sure why. I googled sumproduct & it seems it multiplies corresponding cells of different rows and and adds the products. In this case I'm just adding the totaling the cells that have the correct text in the corresponding cell in a different column.
Also could you explain "simple drop-downn"? I used data validation, putting the 2 optons is 2 cells far outside the table, then the cell referenced in the sumproduct function only takes one of those 2 words. So the user need not type anything, only click. Is that the standard way?
I am excited this is working
Thank you so much
Essta

 

[essta]

Yes, thank you that works, but it requires the user to type hourly or salaried into g6 and possibly misspell. I would like it to be in the call, and they jus have to click it...Does that need code?
essta