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

essta

New Member
Joined
Jun 19, 2019
Messages
5
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Welcome to the forum!
Also put here the formula used.
 
Upvote 0
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
 
Upvote 0
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:

Code:
=SUMPRODUCT((G6=C2:C11)*E2:E11)
 
Last edited:
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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:

Code:
=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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top