Dec 04 2020 03:39 PM
I am trying to count the cells in a column that contain a number value but only if another cell in the same row contains the specified text. (possibly using wrong function?) see below for example
I need to count all the cells contain a number value by lets say month of Dec
Dec 04 2020 03:52 PM
You will need to apply a COUNTIFS function since you are reviewing more than one criteria. See the example below:
Dec 04 2020 09:41 PM
Solution
=+COUNTIFS(E2:E7,E11,F2:F7,"<>")
please find attached screen shot
Month | |
nov | 1 |
dec | 1 |
nov | |
dec | |
nov | 2 |
dec | 2 |
dec | =+COUNTIFS(E2:E7,E11,F2:F7,"<>") |
Dec 05 2020 07:04 AM
Thank you all for the help! this works perfectly!
Go's to show I need to brush up on my excel skills ;)
Dec 05 2020 08:15 AM
I am working on the last section of this excel sheet and I need a formula to figure the commissions earned.
It needs to add all the number values in a column then multiply that by the commission lets say $200 per entry.
I have found a way to do it but its a long and absurd method (needs to be cleaned up bad)
Don't Laugh but this is my poor attempt to try and solve this issue ;)
Each of the purple cells contains a value similar to this (=C8*200)
Dec 05 2020 08:34 AM
Sorry, but that's not clear, at least for me. Where is C8, is 200 fixed for all entries or variable, are column numbers variable or fixed, do you have total column in any case or that's just helper. In general = TotalInRow*200.
Dec 05 2020 08:48 AM
I apologies, I rushed that question started getting busy at the office.
Yes, the 200 is fixed for all entry's in that column but it needs to only count the values by the specified month like before
Dec 05 2020 08:58 AM
Sorry, but I don't see months at all on your screenshot. Could you please give bit more details, desirably with sample file, when you have time.
Dec 05 2020 09:40 AM
Here is a sample of the doc I'm working on.
On the tracker page I am trying to calculate the $200 commissions earned for each home on the data page by month.
Dec 05 2020 09:57 AM
Thank you, but file doesn't explain the logic for me. Why for Jan you calculate sum of working hours * 200, but for Nov Changes*2? I'm not in your business and have no idea what's that about, if you explain more close to Excel terms that will be great.
Dec 05 2020 10:11 AM
The purple is old and is no longer correct, so yeah that wont make any sense just ignore that completely.
I just need a formula to take all the values in the home column in the specified month like Jan or any month and multiply that by $200 to replace the no longer working formula in the "Estimated Monthly Commission Total"
Once I have a working formula for that I'm sure I can edit to fit for the other months and change the commission rate for the different products.
Hope that helped clear things up
Dec 05 2020 10:12 AM
At this point I can just delete the purple mess
Dec 05 2020 01:29 PM
Dec 07 2020 07:07 AM
Dec 04 2020 09:41 PM
Solution
=+COUNTIFS(E2:E7,E11,F2:F7,"<>")
please find attached screen shot
Month | |
nov | 1 |
dec | 1 |
nov | |
dec | |
nov | 2 |
dec | 2 |
dec | =+COUNTIFS(E2:E7,E11,F2:F7,"<>") |