Excel assistance - total depending on another field

Forums › Desktop computing › Excel assistance - total depending on another field

networkn

Networkn
32862 posts

Uber Geek
+1 received by user: 15453

ID Verified

Trusted

Lifetime subscriber

#306409 20-Jul-2023 13:54

I have a sheet with entries broken into months. There are a list of times and the type of plan per month.

There is a column that totals the hours.

I want to set the total to depend on the content of a column and be able to select that.

Is there a nice easy way to do this?

bigreddog

236 posts

Master Geek
+1 received by user: 117

Subscriber

#3106244 20-Jul-2023 14:03

SUMIF

Have a look here for some easy examples Excel SUMIF function | Exceljet (no affiliation with the link - just the first option when I Google'd

Tauranga
Quic Fibre (use R213449EPZJ3R for free setup)

MikeAqua

8024 posts

Uber Geek
+1 received by user: 3817

#3106260 20-Jul-2023 14:28

bigreddog:

SUMIF

Have a look here for some easy examples Excel SUMIF function | Exceljet (no affiliation with the link - just the first option when I Google'd

Or if you want to use multiple columns as criteria for your totals try the SUMIFS function. I use this a lot to get filtered totals from complex data sets. By using the analogous COUNTIFs function, you can calculate a mean value for various, criteria.

If it gets complex, however, it's easier just to use a pivot table.

Mike

networkn

Networkn
32862 posts

Uber Geek
+1 received by user: 15453

ID Verified

Trusted

Lifetime subscriber

#3106275 20-Jul-2023 14:45

Yeah I found SumIF, however for whatever reason it won't return anything.

=SUMIF(H2:H22,"potential text in column O",O2:O22)

It's showing as selecting the right rows in the numbers either side of the : but result is always zero. If I sum the hours column, it's correct.

kiwi_64

276 posts

Ultimate Geek
+1 received by user: 142

Subscriber

#3106290 20-Jul-2023 15:11

not an excel expert, but don't you have your parameters the wrong way around?

If you're "looking for a value in column O", I thought the Column O range was supposed to be the first parameter? (and the values being "summed" are the 3rd parameter)

networkn

Networkn
32862 posts

Uber Geek
+1 received by user: 15453

ID Verified

Trusted

Lifetime subscriber

#3106329 20-Jul-2023 16:28

kiwi_64:

not an excel expert, but don't you have your parameters the wrong way around?

If you're "looking for a value in column O", I thought the Column O range was supposed to be the first parameter? (and the values being "summed" are the 3rd parameter)

Doh. Thank you!