Forum Discussion
formula "if" "and"
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!
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 )
