Sum a column populated by an IF statement based on a pulldown list from another cell

Greetings and thanks for taking the time to help me.
I have a sheet that will be populated with data over the course of a month. I need to sum an entire column as data is entered, which seems easy on the surface. I started with =SUM(D2:D400)

The issue is that column D is populated with either \$45 or \$30 based on what pull-down is selected in Column C. The formula I have in Column D is:

=IF(C2=“Basic”,"\$45",IF(C2=“Indv”,"\$30",IF(C2=“Fam”,"\$30",IF(C2=“LEO”,"\$30",IF(C2="","")))))

I have also tried with and without the leading \$ for the value populated in D. I have also tried to format the column as both general number and currency. The format option seems to have no effect on the cells at all.
When I select something from C, D populates correctly but the sum is still 0 in the cell with =SUM(D2:D400)
If I change the formula to a column with numbers that I have input manually they sum just fine. This leads me to believe that the SUM formula doesn’t recognize the displayed value of the cell populated by the IF statement. I even tried to go over a few columns past what I needed data in and enter =D2 then SUM that column but got the same results.

So how do I SUM a column populated by an IF statement based on a pull-down?

@mr_mike-me,
Is this formula not sufficient for the purpose?
`=IF(C2="";"";IF(C2="Basic";45;30))`

Your IF() statement produces text strings, SUM() ignores cells with text content. Make sure the results are numeric, like

``````=IF(C2="Basic";45;IF(C2="Indv";30;IF(C2="Fam";30;IF(C2="LEO";30;0))))
``````

Note this omits the `IF(C2="";"")` of your expression because it’s superfluous, the final result is 0 if not any condition is met.

2 Likes

Alternativly:

``````SWITCH( C2; "Basic"; 45; "Indv"; 30; "Fam"; 30; "LEO"; 30; ""; 0 )
``````
2 Likes