sumif and if function

  • Thread starter Thread starter Shayla
  • Start date Start date
S

Shayla

Can I combine Sumif and If function?
I have a data spreadsheet with the following columns: DATE column, $$
column, STATE column.
Is there a formula that I can use so that when I update the data spreadsheet
it will automatically calculate for me as what I need below:

- If DATE column is filled in
- then do a SUMIF to calculate $$ for each STATE

I do have some rows without the DATE but $$ will be there, so I just want to
know if there's any formula that I can use
 
Assuming your data A1:C8 header in row 1
Column A holds Date
Column B holds $$
Column C holds State

your criteria in in D2

In E2: =IF(COUNT($A$2:$A$8)=COUNT($B$2:$B$8),SUMIF($C$2:$C$8,D2,$B$2:$B$8),"")
 
Hi

With your data in columns A:C and state list in column E, try this formula
in D1 and copy down to all states.

=SUMPRODUCT(--($A$1:$A$1000<>""),$B$1:$B$1000,--($C$1:$C$1000=E1))

Hopes this helps
 
Back
Top