Complicated question with possible easy answer

[wrestlemy69]

Noob here.
I see 2 possible solutions for the same problem. So first i will try to describe it simply with the possibility to get a simple answer.

I have 4 drop down list. first list is ALL, feed, treats, equipment. second is ALL, chickens, dogs, cats. Third is jan, feb, mar, ....... 4th is 2022, 2023, 2024, ......... on another sheet i have cells with list for each, type, animals, date. I created a sumifs that works for each item individually. But i have not figured out a way to do the ALL for any list.

Is there a way to redifne the word all? meaning all will sum all of the words in another table. Hopefully i explained it well.

Ok so if that is not possible or understandable, here is the next possible solution i see. I could not figure out a way to add the If infront of the Sumifs.

here is the working one

=SUMIFS(Bought!G2:Bought!G332, Bought!I2:Bought!I332, ">="&'Date sheet'!I1,  Bought!I2:Bought!I332, "<="&'Date sheet'!J1, Bought!C2:Bought!C332, B5, Bought!A2:Bought!A332, B4)

B4 is the type list
b5 is the animal list

I feel like i am having a hard time explaing it well.

Thanks in advance for any help

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMIFS(Bought!G2:G332, Bought!I2:I332, ">="&'Date sheet'!I1,  Bought!I2:I332, "<="&'Date sheet'!J1, Bought!C2:C332,if(B5="All","*",B5), Bought!A2:A332, if(B4="All","*",B4))

 

[wrestlemy69]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMIFS(Bought!G2:G332, Bought!I2:I332, ">="&'Date sheet'!I1,  Bought!I2:I332, "<="&'Date sheet'!J1, Bought!C2:C332,if(B5="All","*",B5), Bought!A2:A332, if(B4="All","*",B4))

Thank You so much! That seemed to work perfectly!

 

[Fluff]

Is the 3srd drop down actual text like Jan, Feb etc or is it numbers?

 

[wrestlemy69]

Is the 3srd drop down actual text like Jan, Feb etc or is it numbers?

It is actual text. I was actually just trying to figure out how to do an all for it and year as well. but that gets a bit more complicated. on date sheet i use CONCATENATE as well as vlookup.

 

[Fluff]

It is actual text.

In that case I doubt it's giving a correct sum. If you have Mar in the drop down it will count every month except May, Sep, Oct & Nov.

 

[wrestlemy69]

I did a small test and it worked. I will do a larger test.

datesheetI1 = VLOOKUP(G1,A1:D39,4,0)
datesheetJ1 = VLOOKUP(G1,A1:E39,5,0)
G1 = CONCATENATE('Simple spent'!B6,'Simple spent'!B7) B6 is month B7 is year
a1=oct2022 d1=10/1/2022 e1=10/31/2022 it continues down for each month and year

Not sure this helps, but couldnt figure out a way to upload book

 

[Fluff]

If you're converting the 3rd & 4th drop downs into an actual date then that should be ok.