SUMIF using range from 2 columns

[TAPS_MikeDion]

Hi everybody,

Can SUMIF be used with a range from 2 columns? I'm trying to have it search columns G & H for a string, and if found, retrieve the totals from column I.

This formula currently works for 1 column (column H), but I also need it to search column G as well.

=SUMIF(H4:H382, "Union Station*",I4:I382)

Thanks for the help!

 

[kweaver]

Is this what you mean?

Book2
	G	H	I
1	Union	Plaza	10
2	Town	Hall	20
3	North	Rail	30
4	South	Rail	40
5	Union	Station	50
6	Home	Plaza	60
7	East	Station	70
8	Union	Station	80
9
10			130
Sheet1

Cell Formulas
Range	Formula
I10	I10	=SUMPRODUCT(("Union"=G1:G8)*("Station"=H1:H8)*(I1:I8))

 

[Fluff]

For more than 1 criteria you need sumifs, rather than sumif

=SUMIFS(I4:I382,H4:H382, "Union Station*",G4:G382,"???")

 

[RoryA]

If you want to look for the same text in either of the two columns, then perhaps:

=SUMPRODUCT(((LEFT(H4:H382,13)="Union Station")+(LEFT(G4:G382,13)="Union Station")),I4:I382)

assuming the text won't be in both columns. If it might be:

=SUMPRODUCT(--(((LEFT(H4:H382,13)="Union Station")+(LEFT(G4:G382,13)="Union Station"))>0),I4:I382)

 

[TAPS_MikeDion]

Wow, thank you for so many quick responses!

Unfortunately, as I was trying them all I realized that something else needs to be added to the formula.

Column H would only have "Union Station" as the criteria, but column G would need the "Union Station" criteria along with column H having "Cover Hours" as criteria.

 

[Fluff]

Ok, how about

=SUMPRODUCT((H4:H382="Union Station")+((G4:G382="Union Station")*(H4:H382="Cover Hours")),I4:I382)

 

[TAPS_MikeDion]

Sorry for the very long delay. I was diagnosed with COVID and had been out of work for a week.

First...Fluff buddy, do you ever sleep? LOL

Your formula worked, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

Hope you have recovered.

 

[TAPS_MikeDion]

Not 100% yet, but close. Thank you. I was lucky enough not to lose taste / smell sensations like many have.