formula "if" "and"

Brass Contributor

I need help with calculating a formula for tab 1 (see attachment) that if tab 2 column A contains the month "january" than add the totals for "staff+" (Column C). 

Thank you!

5 Replies

it looks like a COUNTIF or COUNTIFS can be used here. Is column A on tab 2 (month) text or a date? If it is just text "january" (as opposed to a date like 2023-01-01 but formatted to show only the month: 'january') then:
=COUNTIF('Tab 2'!A2:A100, "january")
if that is a date and the date could be any date in Jan then you could use countifs with a date range from 1/1 to 1/31 (but I don't think this is the case so I won't confuse the issue)

that said however I might recommend you format the data in Tab 2 as a table (Home -> 'Format as a Table') and then name that table (e.g. the default is Table1)  and then you can use the following formatting and not worry if your data grows past row 100 in the above example:

=COUNTIF( Table1[month], "january" )

yes tab 2 column will have months as text, then what I want to do is calculate or add the values in column C that correlate to january. I.e if there are 3 lines that have january with the value of 1 in each then I want the sum of all 3 of those to populate in tab 1 in that specific cell.
ok then use SUMIF or SUMIFS
=SUMIFS( Tab2!C:C, Tab2!A:A, "January")
or you could base it on the cell on Tab1 (I think that January is in cell B5)
=SUMIFS( Tab2!C:C, Tab2!$A:$A, $B$5 )

 

jaolvera_0-1702315866777.png

jaolvera_1-1702315887132.png

I am not sure why the formula isnt populating a total of 6?

 

@mtarler 

@jaolvera My guess is that the January don't match because 1 is 'text' and the other is 'date' (unless the values 1,2,3 are text but doubt that).  Just because it shows "January" doesn't mean it is a 'text' value.  See this image:

mtarler_0-1702316229667.png

 

A12 is a 'text' value = "January" but A13 is a 'date' value being shown as the month text.  Notice the formula bar above shows 1/1/2023 even though the cell shows "January".  

If this isn't the problem please share the sheet for me to see what else it might be.  (if you can't post here you can PM it to me)